Excel vba vlookup error

[roy123]

please i need some help with this code below
Dim i As Integer Dim lrow As Long Dim x As Integer
Sheet4.Activate lrow = Sheet4.Range("A" & Rows.count).End(xlUp).Row
For i = 2 To lrow
Cells(i, 1).Activate x = Application.VLookup(ActiveCell.Offset(0, 0).Value, Worksheets(2).Range("A16:B25"), 2, False) If x <> Cells(i, 2).Value Then Cells(i, 2).Interior.Color = RGB(255, 0, 0) Else End If Next i
i have cells to look for in sheet4 and lookup table is in sheet2 range Range("A16:B25")
when i run this code it keeps saying mismatch error
please help

 

[Nine Zero]

I believe your for loop should end with just Next instead of Next i

 

[Nine Zero]

Wait i think i see it.... i think it should be Sheets(2) not Worksheets(2)

 

[roy123]

sorry i tried that but still type mismatch error is popping up.

 

[GWteB]

Try to declare X as a VARIANT ....

 

[Norie]

Try this.

Dim i As Long
Dim lrow As Long
Dim x As Variant

    With sheet4
    
        lrow = .Range("A" & Rows.Count).End(xlUp).Row

        For i = 2 To lrow

            x = Application.VLookup(Cells(i, 1).Value, Worksheets(2).Range("A16:B25"), 2, False)
            If x <> .Cells(i, 2).Value Then
                .Cells(i, 2).Interior.Color = RGB(255, 0, 0)
            End If
        Next i
        
    End With

 

[offthelip]

You never need to use Vlookup in VBA there is much faster and more flexible way of doing the same thing using variant arrays. The way you are doing it will be very slow if you have thousands of rows. The techniques shown in this thread is fast!!
https://www.mrexcel.com/forum/excel-questions/1043185-vlookup-vba-alternative.html