Hi I know there are many questions asked on the same topic as I have here, they don't seem to solve my problem here. I have a task to compare two sheets (A and B) based on column D of both sheets and I want to find and highlight the rows in B that doesn't exist in A. The code goes like this:
Dim i As Long Dim data As Range Dim ref As Range Dim lastRow As Long Worksheets("B").UsedRange Set ref = Worksheets("test").Columns("D:D").Value lastRow = Worksheets("B").UsedRange.Rows.Count Application.ScreenUpdating = False Application.DisplayAlerts = False ' check if data exist in both shts For i = 10 To lastRow Set data = Worksheets("B").Columns("D:D").Find(ref,LookIn:=xlValues, _ LookAt:=xlWhole) If data Is Nothing Then data.EntireRow.Interior.ColorIndex = 36 Exit For Else End If Next
Now the problem is that the line that sets ref is having an error of object required, and the rows are not highlighted.
The screenupdating function is turned off for a shorter run time but it still take some time to run my code, is this the file's or the code's problem?
Advices would be very much appreciated.
UPDATE Task solved using the following code:
With Worksheets("test") For i = 4 To lastRow For j = 4 To lastnewRow If Worksheets("test").Cells(i, 9) = Worksheets("B").Cells(j, 9) Then Exit For Else If j = lastnewRow Then Worksheets("test").Cells(i,9).EntireRow.Interior.ColorIndex = 36 End If End If Next Next End With
Try this code:
Dim shtARng As Range, cell As Range With Worksheets("A") '<--| change "A" with your actual sheet name Set shtARng = .Range("D1", .Cells(.Rows.Count, 4).End(xlUp))) '<--| change "D1" to the column D actual initial row to start your check from End With With Worksheets("B") '<--| change "B" with your actual sheet name For Each cell in .Range("D1", .Cells(.Rows.Count, 4).End(xlUp))) If shtARng.Find(What:=cell.Value, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False) Is Nothing Then cell.EntireRow.Interior.ColorIndex = 36 Next cell End With