[VBA] Two arrays if match found then highlight and repeat.

[montecarlo2012]

Hi.
Working on:

Sub on_N()
Dim rng As Range, fnd As Range
      For Each rng In Range("B2:F2") ' READ
            Set fnd = Range("R2:R37").Find(rng, LookIn:=xlValues, lookat:=xlWhole) '||||||||||HIGHLIGH
                   If Not fnd Is Nothing Then
                       fnd.Interior.ColorIndex = 7
                   End If
      Next rng
End Sub

and also try:

Sub CompareTwoRanges()
        Set B = Range("B2:F2") ' READ
        Set M = Range("R2:R37") ' WRITE
                  For Each c1 In M 'write k2
                  For Each c2 In B 'read b2
                             If c1.Value = c2.Value Then
                                c1.Interior.ColorIndex = 38
                             End If
                  Next
                  Next
End Sub

So right here just for one time, work good, my week point is how to repeat this.

I have an array B2:F38 to be compare against H2:AJ

Range by range.
On A1 notation looks like this.

If any cell on B2:F2 match H2:H37 then highlight the duplicates
If any cell on B3:F3 match H3:H37 then highlight the duplicates
If any cell on B4:F4 match H4:H37 then highlight the duplicates
If any cell on B5:F5 match H5:H37 then highlight the duplicates

Thank you for reading my post, I hope to hear from you
"-comments on the code are highly appreciated-"

 

[bebo021999]

Its unlear for me. What exactly H2:AJ?
Could you post "before" and "after" screenshot?

 

[montecarlo2012]

Sorry, you are right, big mistake.
Thank you, now I realize my A1 notation is wrong.
If any cell on B2:F2 match H2:H37 then highlight the duplicates
If any cell on B3:F3 match I2:I37 then highlight the duplicates
If any cell on B4:F4 match J2:J37 then highlight the duplicates
If any cell on B5:F5 match K2:K37 then highlight the duplicates

Until

If any cell on B37:F37 match AQ2:AQ37 then highlight the duplicates

excellent catch, if you don't see this everything was wrong, wow, thank you
now I am good.
Thank you.

 

[bebo021999]

Sub match()
Dim i, j, k  As Long
Range("a1:aq37").Interior.Color = xlNone
For i = 2 To 36 ' B2:F2 down to B36:F36
    For j = 2 To 6 ' read B2,C2,...,F2
        For k = 2 To 36 ' read H2,H3,...,H36
            If Cells(i, j) = Cells(k, 6 + i) Then ' compare each cell in both range
                Cells(i, j).Interior.Color = RGB(255, 255, 0)
                Cells(k, 6 + i).Interior.Color = RGB(255, 0, 0)
            End If
        Next
    Next
Next
End Sub

 

[montecarlo2012]

Work really good thank you bebo021999, if you don't mind, when you resolve code you just type and type or you write first some guidelines first, I am curious about your logic. thank you.

 

[montecarlo2012]

bebo, sorry, I didn't realize something, the code run and highlight only 14 columns, do not run for the 36.

 

[montecarlo2012]

bebo, your code is fine, was my mistake, is perfect sorry.