Steven,

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo NotFound:
With Target
.Value = Application.WorksheetFunction.VLookup( _
.Value, Range("Categories"), 2, False)
End With
GoTo Found:
NotFound:
Target.Value = ""

Found:
Application.EnableEvents = True

End Sub


Note that using 1 in a VLOOKUP just returns the value that you were matching - in other words, it
does nothing useful. Unless you didn't want to use VLOOKUP, but use INDEX or some other
function....

HTH,
Bernie
MS Excel MVP


"Steven" wrote in message
...
I was give this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Const sCell as string = "$A$1"
Const sListRange as string = "$D$1:$D$3"

Application.EnableEvents = False
With Range(sCell)
If Target.Address = sCell Then
.Value = Application.WorksheetFunction.VLookup( _
.Value, Range(sListRange), 1, False)
End If
End With
Application.EnableEvents = True
End Sub

It works fine except instead of = "$A$1" I want it to to be the ActiveCell
and instead of = "$D$1:$D$3" I want it to be an already existing named range
"Categories"

And if a match is not found then to make the current cell blank.

Thank you for your help.

Steven