VBA If Value = Vlookup error

[JonesKi]

Hi Guys,

having a problem with some VBA.

Its working until the Vlookup returns a "#N/a" then i get a:
Run-time error '1004'
unable to get the vlookup property of the worksheet funtion class.

any ideas?

i need it to check a selection of cells and highligh if they match a list in workbook range it refers to.

---

Sub Exclude()
For Each X In Selection
Set wbk = Workbooks.Open("D:\Myfile")
Set range1 = wbk.Sheets(1).Range("A2:A829")
If X.Value = Application.WorksheetFunction.VLookup(X.Value, range1, 1, False) Then


With X.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.color = 2228049
.TintAndShade = 0
.PatternTintAndShade = 0

End With


End If

Next


End Sub

 

[VoG]

Try

Sub Exclude()
For Each x In Selection
Set wbk = Workbooks.Open("D:\Myfile")
Set range1 = wbk.Sheets(1).Range("A2:A829")
v = Application.VLookup(x.Value, range1, 1, False)
If Not IsError(v) Then
If x.Value = v Then


With x.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 2228049
.TintAndShade = 0
.PatternTintAndShade = 0

End With


End If
End If
Next


End Sub

 

[Jonmo1]

Try

Sub Exclude()
Dim MyVal As Variant
Dim wbk As Workbook, range1 As Range, x As Long

Set wbk = Workbooks.Open("D:\Myfile")
Set range1 = wbk.Sheets(1).Range("A2:A829")
For Each x In Selection
    MyVal = Application.Match(x.Value, range1, 0)
    If Not IsError(MyVal) Then
        With x.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 2228049
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    End If
Next
End Sub

 

[JonesKi]

Perfect!! Thanks guys

 

[Jonmo1]

You're welcome.