vba arrays

[montecarlo2012]

Hi.
Please, I am trying in a very basic ordinary way to compare two arrays.
I need to compare every row on Sheet 2 to every row on sheet 1 and highlight on 1
only as ilustration what I am doing is this:

Sub t()
Dim A As Integer, B As Integer, C As Integer, D As Integer, E As Integer, F As Integer

B = Sheet2.Range("H2").Value
A = Sheet1.Range("B2").Value
If A = B Then
Sheet1.Range("B2").Interior.ColorIndex = 6
Else
Sheet1.Range("B2").Interior.ColorIndex = 0
End If
'/////////////////////////////////////////////////////////////////////////
C = Sheet2.Range("I2").Value
D = Sheet1.Range("C2").Value
If C = D Then
Sheet1.Range("C2").Interior.ColorIndex = 6
Else
Sheet1.Range("C2").Interior.ColorIndex = 0
End If
'//////////////////////////////////////////////////////////////////////////
E = Sheet2.Range("J2").Value
F = Sheet1.Range("D2").Value
If E = F Then
Sheet1.Range("D2").Interior.ColorIndex = 6
Else
Sheet1.Range("D2").Interior.ColorIndex = 0
End If
End Sub

for better understanding about what I try.
thanks

 

[montecarlo2012]

Unfortunately XL2BB do not work in my Pc, sorry.
so a picture maybe show a little the idea. thanks.

 

[DanteAmor]

I've fixed the code a bit to make it easier to adjust the columns.

Sub compare_two_arrays_2()
  Dim a As Variant, b As Variant
  Dim r As Range, i As Range, j As Range, k As Long
  
  Application.ScreenUpdating = False
  Set r = Sheets("Sheet2").Range("L2:Q" & Sheets("Sheet2").Range("L:Q").Find("*", , xlValues, , 1, 2).Row)
  r.Interior.ColorIndex = 0
  For Each i In r.Rows
    k = 0
    For Each j In i.Columns
      If j.Value = Sheets("Sheet5").Range("B" & j.Row).Offset(, k).Value Then j.Interior.ColorIndex = 6
      k = k + 1
    Next
  Next
  Application.ScreenUpdating = True
End Sub

 

[montecarlo2012]

DanteAmor, Thank you. Work Perfect. You are awesome.
So now if I need to compare two, with another one, I just chance the sheet numbers, great,
I have three more sheets so they are in the same spot,
Sheet 3 (“R1:W34200”)
Sheet 4 (“R1:W35000”)
Sheet 10 (“R1:W900”)
so I will do this inserting a module for each one,
Any sugestion or comment will be appreciated.
thank you.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.