Find and highlight the difference

advertisements

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