VLOOKUP returns nothing / Macro gives me an error message

[Blanchetdb]

Hi

I presently have vlookup formulas in numerous cells that are linked to a value entered in cell F18 and a macro is linked to a value in cell H31

I need a macro that will generate a message box if the number entered in cell F18 is not found during the vlookup on Sheet2 which in turn will not populate cell H31 but if the number is found then the macro is activated

I presently have the following

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$F$18" Then
        Call Language
    End If
End Sub

and the macro triggered by the vlookup is ... the vlookup is a formula and not used in the macro

Sub Language()

Sub Language()

ThisWorkbook.Worksheets("Sheet1").Unprotect ("termbuilder2020")

If Range("AE30") > 0 And (Range("E33") = "E" Or Range("E33") = "C" Or Range("E33") = Range("T31")) Then
Range("K33") = "Meets Requirements"
Range("K33").Font.Color = vbGreen
Else
Range("K33") = "Does Not Meet Requirements"
Range("K33").Font.Color = vbRed
End If

If Range("AE31") > 0 And (Range("E34") = "E" Or Range("E34") = "C" Or Range("E34") = Range("T32")) Then
Range("K34") = "Meets Requirements"
Range("K34").Font.Color = vbGreen
Else
Range("K34") = "Does Not Meet Requirements"
Range("K34").Font.Color = vbRed
End If

ThisWorkbook.Worksheets("Sheet1").Protect ("termbuilder2020")


End Sub

any help would be greatly appreciated

 

[StephenCrump]

Are you looking for something along these lines:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("F18")) Is Nothing Then
        If IsError(Range("YourVLookupResult").Value) Then
            MsgBox "VLookup not found!"
        Else
            'Code to populate H31
        End If
    End If

End Sub

 

[Blanchetdb]

after numerous tries on my own, I finally got it to work but I will most definitely try your macro and let you know....thanks