Highlight 3 way patterns in the 3 different colours.

[Kishan]

Using Excel 2000
Hi,

I need VBA, which can do the following task...

1-Column C:D rows highlight in the colour "Light Pink", in the case if finds any same or reversal patterns in the Columns E:F, G:H, I:J, K:L, M:N or O:P same row.

2-Highlight 3 patterns, which can be the same and can be consider as reversals too. Which are these patterns 1-"11", 2-"XX" and 3rd one is "22" in the yellow colour.

3-Rest of reversal remaining patterns I need to highlighted in the colour "Sky Blue"


Example....

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
6			2	2	1	2	X	1	X	X	1	1	1	X	2	1
7			X	X	1	2	1	X	1	1	1	X	X	1	2	1
8			1	2	X	1	X	1	2	X	2	1	1	2	1	X
9			1	2	1	1	2	2	1	1	X	2	2	X	X	2
10			2	2	1	2	1	1	1	X	1	1	2	2	1	X
11			X	1	1	2	2	2	1	2	1	1	1	1	X	2
12			2	1	1	2	1	X	2	1	1	1	1	1	1	1
13			2	1	2	2	2	X	1	2	X	1	2	X	1	2
14			1	2	1	2	X	X	1	1	1	1	1	2	X	2
15			2	1	1	2	X	X	2	X	1	X	1	X	X	2
16			X	X	1	1	X	1	1	1	1	2	1	X	2	1
17			2	1	1	X	1	2	X	1	1	2	1	X	X	1
18			1	1	1	X	1	X	2	1	2	1	X	X	2	2
19			1	2	X	1	X	1	1	2	2	1	1	1	2	2
20			1	1	2	1	2	X	1	1	2	X	X	1	1	1
21			X	X	1	2	1	1	X	1	2	1	2	1	X	1
22			1	1	2	1	1	1	1	2	X	2	1	1	X	X
23			X	1	1	1	2	2	2	1	1	1	X	2	2	X
24			1	X	X	1	X	1	X	1	2	2	1	1	1	1
25			X	2	1	X	X	1	X	X	1	1	2	1	1	1
26			2	X	1	1	1	1	X	1	2	X	X	1	1	X
27			1	1	X	X	1	X	1	1	1	X	1	X	1	X
28			X	2	X	1	1	1	2	1	1	1	1	2	2	2
29			2	2	1	2	1	X	1	X	X	2	1	X	1	2
30			X	X	X	1	2	2	X	1	X	2	1	1	2	1
31			2	1	X	1	1	1	X	1	2	2	1	1	X	2
32			1	2	X	1	1	1	X	2	1	2	1	1	2	X
33			1	X	2	X	2	X	X	1	1	2	2	2	X	2
34			X	2	2	1	X	1	1	2	X	X	2	X	1	1
35			1	2	2	1	2	2	1	1	2	2	X	X	2	1
36			X	1	X	2	1	X	2	1	2	2	2	1	X	1
37			2	X	X	2	1	2	2	1	1	X	2	1	2	X
38			X	1	X	X	2	1	X	X	2	X	X	X	1	1
39			X	X	1	1	1	2	X	1	X	1	1	1	1	1
40			2	2	X	X	X	1	1	X	1	2	2	X	2	X
41			2	1	1	2	2	2	X	X	1	2	2	2	1	X
42			X	X	1	2	2	X	1	1	X	X	X	1	2	1
43			1	X	X	1	2	X	1	2	X	X	X	1	X	2
44
45
46
Sheet7

Thank you in advance

Regards,
Kishan

 

[Peter_SSs]

You can fiddle with the detail of the colour but here a concept

Sub FindMatches()
  Dim r As Long, c As Long, clr1 As Long, clr2 As Long
  
  For r = 2 To Range("C" & Rows.Count).End(xlUp).Row
    If Cells(r, 3).Value = Cells(r, 4).Value Then
      clr1 = vbYellow
      clr2 = clr1
    Else
      clr1 = vbRed
      clr2 = vbBlue
    End If
    For c = 5 To 15 Step 2
      If Cells(r, c).Value = Cells(r, 3).Value And Cells(r, c + 1).Value = Cells(r, 4).Value Then
        Cells(r, c).Resize(, 2).Interior.Color = clr1
        Cells(r, 3).Resize(, 2).Interior.Color = vbRed
      ElseIf Cells(r, c + 1).Value = Cells(r, 3).Value And Cells(r, c).Value = Cells(r, 4).Value Then
        Cells(r, c).Resize(, 2).Interior.Color = clr2
        Cells(r, 3).Resize(, 2).Interior.Color = vbRed
      End If
    Next c
  Next r
End Sub

 

[Kishan]

You can fiddle with the detail of the colour but here a concept

Sub FindMatches()
  Dim r As Long, c As Long, clr1 As Long, clr2 As Long
  
  For r = 2 To Range("C" & Rows.Count).End(xlUp).Row
    If Cells(r, 3).Value = Cells(r, 4).Value Then
      clr1 = vbYellow
      clr2 = clr1
    Else
      clr1 = vbRed
      clr2 = vbBlue
    End If
    For c = 5 To 15 Step 2
      If Cells(r, c).Value = Cells(r, 3).Value And Cells(r, c + 1).Value = Cells(r, 4).Value Then
        Cells(r, c).Resize(, 2).Interior.Color = clr1
        Cells(r, 3).Resize(, 2).Interior.Color = vbRed
      ElseIf Cells(r, c + 1).Value = Cells(r, 3).Value And Cells(r, c).Value = Cells(r, 4).Value Then
        Cells(r, c).Resize(, 2).Interior.Color = clr2
        Cells(r, 3).Resize(, 2).Interior.Color = vbRed
      End If
    Next c
  Next r
End Sub

Peter_SSs, splendid! I will play with the colours, the significant it worked as appeal. Thank you so much for your help</SPAN></SPAN>

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

 

[Peter_SSs]

Glad it helped.