Highlight 3 way patterns in the 3 different colours.

Kishan

Well-known Member
Joined
Mar 15, 2011
Messages
1,648
Office Version
  1. 2010
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQR
1
2
3
4
5P1P2P3P4P5P6P7P8P9P10P11P12P13P14
62212X1XX111X21
7XX121X111XX121
812X1X12X21121X
912112211X22XX2
102212111X11221X
11X11222121111X2
1221121X21111111
1321222X12X12X12
141212XX111112X2
152112XX2X1X1XX2
16XX11X111121X21
17211X12X1121XX1
18111X1X2121XX22
1912X1X112211122
2011212X112XX111
21XX1211X12121X1
2211211112X211XX
23X111222111X22X
241XX1X1X1221111
25X21XX1XX112111
262X1111X12XX11X
2711XX1X111X1X1X
28X2X11121111222
2922121X1XX21X12
30XXX122X1X21121
3121X111X12211X2
3212X111X212112X
331X2X2XX11222X2
34X221X112XX2X11
351221221122XX21
36X1X21X212221X1
372XX212211X212X
38X1XX21XX2XXX11
39XX1112X1X11111
4022XXX11X122X2X
41211222XX12221X
42XX122X11XXX121
431XX12X12XXX1X2
44
45
46
Sheet7


Thank you in advance

Regards,
Kishan
 
Last edited:

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
You can fiddle with the detail of the colour but here a concept
Code:
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
 
Last edited:
Upvote 0
You can fiddle with the detail of the colour but here a concept
Code:
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>
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top