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

DMO123

Board Regular
Joined
Aug 16, 2018
Messages
99
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 :)
 
How about
Code:
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?
 
Upvote 0

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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.
 
Upvote 0
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.
 
Upvote 0
That still needs a new thread thanks.
 
Upvote 0

Forum statistics

Threads
1,213,537
Messages
6,114,216
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top