Vlookup in VBA within a for loop [duplicate]

Question

I've been having trouble getting this macro to work. I want it to loop through a range and highlight a cell if it does not equal the corresponding value on another sheet through the vlookup function. But I keep getting an error with this code:

For Each cell In Worksheets("Sheet1").Range("A2:A1000")
    If cell <> Application.WorksheetFunction.VLookup(cell, Worksheets("Sheet2").Range("C3:E128"), 3, 0) Then
        cell.Interior.Color = 65535
    Else
    End If
Next cell

it keeps returning

Run-time error '1004': Unable to get the VLookup property of the WorksheetFunction class

any insight is much appreciated!!

Answer 1

You are getting that error because the VLookup is not able to find and return anything. There are various ways to handle it. Here is one example.

Sub Sample()
    Dim cell As Range
    Dim Ret

    For Each cell In Worksheets("Sheet1").Range("A2:A1000")
        On Error Resume Next
        Ret = Application.WorksheetFunction.VLookup(cell, _
              Worksheets("Sheet2").Range("C3:E128"), 3, 0)
        On Error GoTo 0

        If Ret <> "" Then
            If cell <> Ret Then
                cell.Interior.Color = 65535
            End If
            Ret = ""
        End If
    Next
End Sub

Answer 2

Try this code

WorksheetFunction.Vlookup v/s Application.Vlookup

On Error Resume Next

For Each cell In Worksheets("Sheet1").Range("A2:A1000")
    Result = Application.VLookup(cell, Worksheets("Sheet2").Range("C3:E128"), 3, 0)

    If Result = "Error 2042" Then
        'nothing found
    ElseIf cell <> Result Then
        cell.Interior.Color = 65535
    End If

Next

On Error GoTo 0