VBA to compare rows in two different sheets and if they match highlight in red

[DMO123]

Hi, i am new to this board. i have been searching online and cannot seem to find much help around what i am trying to do.

i am looking to compare two sheets in one workbook. for example, if sheet 1 row 1 matches a row in sheet 2 then highlight it red if not leave blank. i tried:

Sub Test_Sheet()
Dim r As Range
Dim s As Range


Set s = Sheets("Already Billed").Columns(1)


For Each r In ActiveSheet.UsedRange.Rows
If Not (s.Find(r.Cells(1, 1).Value) Is Nothing) Then
r.Interior.ColorIndex = 3
End If
Next r
End Sub

but if i change the data in column 3 it does not register as the above is only looking in column 1 but i need the whole row to be check.

can someone help

thanks

 

[karpik92]

How about

Sub CheckRows()
   Dim cl As Range
   Dim Ws1 As Worksheet
   Dim Ws2 As Worksheet
   Dim Vlu As String
   Dim Lc As Long
   
   Set Ws1 = Sheets("Sheet1")
   Set Ws2 = Sheets("sheet2")
   Lc = Ws2.Cells(1, Columns.Count).End(xlToLeft).Column
   With CreateObject("scripting.dictionary")
      For Each cl In Ws2.Range("A2", Ws2.Range("A" & Rows.Count).End(xlUp))
         Vlu = Join(Application.Index(cl.Resize(, Lc).Value, 1, 0), "|")
         .Item(Vlu) = Empty
      Next cl
      For Each cl In Ws1.Range("A2", Ws1.Range("A" & Rows.Count).End(xlUp))
         Vlu = Join(Application.Index(cl.Resize(, Lc).Value, 1, 0), "|")
         If .exists(Vlu) Then cl.Resize(, Lc).Interior.Color = vbRed
      Next cl
   End With
End Sub

How would I adjust this to only not highlight the cell in that row that is different. So for example if something changes in that row I only want to see the cell that changed and not the entire row?

 

[Fluff]

What you are asking for is the opposite of the OP.
Please start a thread of your own, giving details of what you want & how your data is laid out.

 

[karpik92]

What you are asking for is the opposite of the OP.
Please start a thread of your own, giving details of what you want & how your data is laid out.

I think I just worded it weirdly - apologies. This code works great for me - however instead of highlighting the entire row that matches I would like it highlight the cells in each row that match and not highlight the ones that do not. Please let me know if you think this is still different and should be started in a new thread.

 

[Fluff]

That still needs a new thread thanks.