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

 

[DanteAmor]

Compare each row starting at row 2, and I guess, up to the last row with data, this is clear to me.
What columns do you want to compare, only 3 columns: B, C, D against H, I, J?

 

[montecarlo2012]

Thank you, DanteAmor, Yes is to compare the whole array 6 columns from B2:G to the LastRow and the other array also is H:M to the lastrow as well, Thank you. and highlight on the first one the duplicates.

 

[DanteAmor]

B2:G to the LastRow and the other array also is H:M

But B2:G2 vs. H2:M2, B3:G3 vs. H3:M3, B4:G4 vs. H4:M4, and so on.
Or B2:G2 vs. H:M all records, B3:G3 vs. H:M all records, B4:G4 vs. H:M all records, and so on.

 

[montecarlo2012]

Thanks Mr. DanteAmor. and yes you are right.
B2:G2 VS H2:M2 / B3:G3 VS H3:M3 / YES and so on.
every row against every row.

 

[DanteAmor]

Try this

Sub compare_two_arrays()
  Dim a As Variant, b As Variant
  Dim r As Range, i As Range, j As Range
  
  Application.ScreenUpdating = False
  Set r = Sheets("Sheet1").Range("B2:G" & Sheets("Sheet1").Range("B:G").Find("*", , xlValues, , 1, 2).Row)
  r.Interior.ColorIndex = 0
  For Each i In r.Rows
    For Each j In i.Columns
      If j.Value = Sheets("Sheet2").Cells(j.Row, j.Column + 6).Value Then j.Interior.ColorIndex = 6
    Next
  Next
  Application.ScreenUpdating = True
End Sub

 

[montecarlo2012]

Mr. DanteAmor. Thanks for your code.
I run this code but Nothing happen, even I run with F8 just to be sure, but still, no results at all.

 

[DanteAmor]

You can share a sample of your data from sheet1 and sheet2. Use XL2BB tool, look at my signature.

 

[montecarlo2012]

DanteAmor. Sorry, the test was in my job, Now at home I did again
and
WORK GOOD
THANK YOU
YOU ARE GREAT.

 

[montecarlo2012]

Sorry, if I bother You, Mr. DanteAmor, I tried with another sheet, I am a little frustrated, I am trying to use the same code with different sheets, do you think is possible for you to give me a little extra help, Please.
this is what I tried

Sub compare_two_arrays()
  Dim a As Variant, b As Variant
  Dim r As Range, i As Range, j As Range
  
  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
    For Each j In i.Columns
      If j.Value = Sheets("Sheet5").Cells(j.row, j.Column + 1).Value Then j.Interior.ColorIndex = 6
    Next
  Next
  Application.ScreenUpdating = True
End Sub

sheet 5 have the array at B2:G
and sheet 2 L2:G
I would like to see the duplicates highlight on sheet 2, and the same idea, row by row to the end.
Thanks ahead for any xtra-help.