Compare 2 sheets with criteria

[danjw_98]

I am trying to compare the differences of changed data on 2 sheets using the below VBA but am not getting it to work.
any help would be appreciated...thanks.

Sub Compare2()
Dim rngCell As Range
Sheets("sheet1").Select
finalRow = Range("A65536").End(xlUp).Row
For x = 2 To finalRow
sh1a = Range("b" & x).Value
sh1b = Range("f" & x).Value


Sheets("sheet2").Select
finalRow = Range("A65536").End(xlUp).Row
For y = 2 To finalRow
sh2a = Range("b" & y).Value
sh2b = Range("f" & y).Value
If sh1a = sh2a And sh1b = sh2b Then

For Each rngCell In Worksheets("Sheet1").Range("g" & x & ":j" & x)
If Not rngCell.Value = Worksheets("Sheet2").Cells("f" & y & ":j" & y).Value Then
rngCell.Interior.Color = vbYellow
End If
Next

End If
Next y
Next x
End Sub

 

[onlyadrafter]

Hello,

what cells are you trying to compare? is it Sheet1 cell G2 to sheet 2 cell F2?

Sub COMPARE_TWO_SHEETS()
    For Each MY_CELL In Sheets("Sheet1").Range("G2:J" & Range("J" & Rows.Count).End(xlUp).Row)
        If MY_CELL <> Sheets("Sheet2").Range(MY_CELL.Address).Offset(0, -1).Value Then
            MY_CELL.Interior.Color = vbYellow
            Sheets("Sheet2").Range(MY_CELL.Address).Offset(0, -1).Interior.Color = vbYellow
        End If
    Next MY_CELL
End Sub

 

[danjw_98]

thanks for check on this for me....

sheet1 is the existing data that I am comparing to sheet2 that is the new data. sheet 1 would need to be updated with the new data and the cells that changed would be highlighted yellow.

hopefully this helps and thanks..

 

[onlyadrafter]

Hello,

to compare cells in Columns G:J, cell for cell, then try this:

Sub COMPARE_TWO_SHEETS()
    For Each MY_CELL In Sheets("Sheet1").Range("G2:J" & Range("J" & Rows.Count).End(xlUp).Row)
        If MY_CELL <> Sheets("Sheet2").Range(MY_CELL.Address).Value Then
            MY_CELL.Value = Sheets("Sheet2").Range(MY_CELL.Address).Value
            MY_CELL.Interior.Color = vbYellow
        End If
    Next MY_CELL
End Sub