Typical matches; highlight and count them

[Kishan]

Using Excel 2000</SPAN></SPAN>

Hi,</SPAN></SPAN>

I want to find match of column C&D in the entire row and want to count the match</SPAN></SPAN>

Matches method example....</SPAN></SPAN>
Row 6 cells C6:D6 pattern "X1" look through in the column E:P in the row 6... following this method highlight each row column C:D patterns</SPAN></SPAN>

Note: once match find don't look back go for forward match only, example row 8 C8:D8 pattern "22" F8, G8, H8 has 2, 2, 2 in this case could be highlighted all 3, but as F8:G8 matched so look for H8:I8 if not match live it </SPAN></SPAN>

Below example show some matches</SPAN></SPAN>

Please need VBA help?</SPAN></SPAN>

Result data example</SPAN></SPAN>

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2
3
4
5			P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12	P13	P14	Count Match
6			X	1	2	1	2	X	X	1	1	2	X	1	1	2	3
7			1	X	2	2	2	1	X	X	2	2	X	X	1	1	2
8			2	2	1	2	2	2	X	X	2	2	2	2	2	X	4
9			1	X	1	2	2	1	X	1	1	X	1	2	2	1	3
10			X	1	X	1	X	1	1	X	1	1	1	X	X	1	5
11			X	X	X	1	1	1	1	X	1	X	2	1	2	X	1
12			2	X	1	1	1	2	X	X	X	1	1	1	2	1	1
13			X	1	X	X	1	1	1	1	1	1	1	X	1	1	3
14			1	X	X	1	2	1	2	1	1	1	2	X	2	X	1
15			X	2	X	1	1	X	1	1	1	1	X	1	1	X	1
16			1	1	1	1	1	1	X	X	X	1	1	1	2	X	4
17			1	2	1	2	X	1	1	2	X	2	1	1	1	X	3
18			1	X	X	1	1	2	X	X	1	X	1	1	X	1	3
19			1	X	X	1	1	2	1	1	2	2	1	X	1	1	2
20			1	1	1	X	1	1	1	1	X	1	1	1	1	1	5
21			X	1	2	X	1	1	1	2	X	1	1	2	X	X	3
22			1	1	1	X	1	X	X	1	X	1	1	1	1	1	3
23			1	1	1	1	1	X	X	2	1	1	1	1	1	2	4
24			1	1	1	1	1	1	1	1	1	1	2	X	X	1	5
25			1	2	2	1	1	1	1	1	1	1	1	X	1	1	1
26			1	1	X	1	1	1	1	1	1	1	1	1	2	1	5
27			X	X	X	1	1	1	X	1	1	2	X	1	1	1	1
28			X	2	1	1	2	1	1	1	1	1	X	X	1	X	1
29			1	X	2	2	1	1	1	2	X	X	1	1	1	X	2
30			2	1	X	X	1	1	X	X	1	X	1	1	1	1	1
31			1	X	1	2	2	1	1	1	1	2	1	1	1	X	2
32			1	1	X	1	2	2	2	1	1	X	1	X	1	X	2
33			2	1	X	1	X	1	X	1	1	1	1	1	1	1	1
34			X	1	1	1	1	1	2	1	2	X	X	1	X	X	2
35			1	2	1	1	X	1	1	1	X	2	X	1	X	X	1
36			X	1	1	1	1	X	1	2	1	1	X	1	X	X	3
37			2	X	1	1	1	X	X	1	1	X	1	1	X	1	1
38			1	X	2	X	1	X	1	1	1	1	1	2	1	X	3
39			X	1	X	X	1	X	1	1	X	1	2	2	1	1	3
40			1	X	X	1	2	X	1	1	X	1	X	1	2	1	3
41			1	X	1	1	X	1	X	X	2	1	1	1	1	X	4
42			1	2	1	1	1	1	X	1	X	X	1	X	2	2	1
43			X	1	1	1	1	1	1	1	2	1	2	2	1	X	1
44			2	X	X	1	1	X	1	1	X	X	X	X	1	X	1
45			X	2	1	1	X	1	1	2	1	1	1	1	2	1	1
46			1	X	X	1	1	1	1	1	1	1	X	1	1	X	3
47			X	X	1	1	1	X	X	2	1	1	1	1	X	X	3
48			1	1	1	X	1	X	1	1	1	1	X	1	1	1	4
49			X	X	1	1	X	1	1	1	1	1	1	1	1	1	1
50			1	1	1	X	1	1	1	1	2	1	1	X	1	1	5
51			1	1	1	1	1	X	X	1	X	2	X	X	X	X	2
52			1	1	1	1	2	1	X	X	X	1	1	1	1	1	4
53			2	X	1	1	X	1	1	X	X	1	1	1	1	2	1
54			1	X	X	1	1	1	1	1	1	2	1	2	1	2	1
55			1	1	1	1	X	1	1	X	X	X	1	1	X	1	4
56			X	1	1	1	X	1	2	1	1	1	2	X	1	1	3
57			1	1	X	X	1	X	1	1	X	X	X	2	1	1	3
58			1	2	2	1	1	1	X	2	1	X	X	X	1	X	1
59			1	1	X	1	1	2	1	1	1	1	X	1	X	1	4
60			1	2	2	1	1	X	1	1	1	1	1	X	1	X	1
61			1	1	1	1	2	1	X	1	2	2	1	X	1	X	2
62			2	1	1	X	1	1	1	X	1	1	X	X	2	X	1
63			1	1	1	1	X	1	1	X	1	1	2	1	1	2	5
64			1	1	1	1	1	1	2	1	2	1	1	1	X	1	4
65			1	2	1	1	2	2	2	2	1	1	1	1	2	X	3
66			1	2	2	1	1	1	1	1	1	X	X	1	1	X	1
67			1	1	1	1	X	1	X	X	X	2	2	1	2	1	2
68			1	1	X	X	1	1	1	1	1	1	1	2	1	1	5
69			1	X	1	X	1	1	2	1	1	1	X	1	X	1	3
70			X	1	1	1	X	1	1	1	1	1	X	1	2	X	3
71			1	1	1	2	1	X	1	1	X	1	1	1	1	X	4
72			1	1	X	1	1	X	1	1	1	1	X	1	1	1	5
73			1	X	X	2	X	1	X	X	2	1	1	X	1	2	1
74			X	X	1	X	1	X	X	1	X	X	1	1	1	2	2
75
76
Sheet1

Thank you in advance</SPAN></SPAN>

Regards,</SPAN></SPAN>
Kishan</SPAN></SPAN>

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG06Sep18
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, R [COLOR="Navy"]As[/COLOR] Variant, ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("C6", Range("C" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    R = Array(Dn.Value, Dn.Offset(, 1).Value)
        [COLOR="Navy"]For[/COLOR] ac = 2 To 14
            [COLOR="Navy"]If[/COLOR] Dn.Offset(, ac) = R(0) And Dn.Offset(, ac + 1) = R(1) [COLOR="Navy"]Then[/COLOR]
                c = c + 1: ac = ac + 1
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] ac
        Dn.Offset(, 14) = c + 1: c = 0
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Kishan]

Try this:-

[COLOR=navy]Sub[/COLOR] MG06Sep18
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

Hi Mick, thank you for the quick response. The code counts are perfect, but does not highlights the matches in the rows, may I did not explained well I also need matches should be coloured in 2 different colours by alternating rows</SPAN></SPAN>

Please it could be possible </SPAN></SPAN>

Thank you in advance</SPAN></SPAN>

Regards,</SPAN></SPAN>
Kishan </SPAN></SPAN>

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG06Sep24
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, R [COLOR="Navy"]As[/COLOR] Variant, ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] col [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("C6", Range("C" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    R = Array(Dn.Value, Dn.Offset(, 1).Value)
     col = IIf(col = vbGreen, vbYellow, vbGreen)
       Dn.Resize(, 2).Interior.Color = col
        [COLOR="Navy"]For[/COLOR] ac = 2 To 14
            [COLOR="Navy"]If[/COLOR] Dn.Offset(, ac) = R(0) And Dn.Offset(, ac + 1) = R(1) [COLOR="Navy"]Then[/COLOR]
               Dn.Offset(, ac).Resize(, 2).Interior.Color = col
                c = c + 1: ac = ac + 1
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] ac
        Dn.Offset(, 14) = c + 1: c = 0
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Kishan]

Try this:-

[COLOR=navy]Sub[/COLOR] MG06Sep24
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, R [COLOR=navy]As[/COLOR] Variant, ac [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] c [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] col [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Set[/COLOR] Rng = Range("C6", Range("C" & Rows.Count).End(xlUp))
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
    R = Array(Dn.Value, Dn.Offset(, 1).Value)
     col = IIf(col = vbGreen, vbYellow, vbGreen)
       Dn.Resize(, 2).Interior.Color = col
        [COLOR=navy]For[/COLOR] ac = 2 To 14
            [COLOR=navy]If[/COLOR] Dn.Offset(, ac) = R(0) And Dn.Offset(, ac + 1) = R(1) [COLOR=navy]Then[/COLOR]
               Dn.Offset(, ac).Resize(, 2).Interior.Color = col
                c = c + 1: ac = ac + 1
            [COLOR=navy]End[/COLOR] If
        [COLOR=navy]Next[/COLOR] ac
        Dn.Offset(, 14) = c + 1: c = 0
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

Mick, yes this is it!! Both colouring and count are perfect!!</SPAN></SPAN>

Thank you so much; I do appreciate your help and time and for solving it twice</SPAN></SPAN>

Have a great day</SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Kishan </SPAN></SPAN>

 

[MickG]

You're very welcome