Private Sub Worksheet_Change(ByVal Target As Range)
Dim res as Variant
On Error GoTo NotFound:
If ActiveCell.Column = 3 Then

res= Application.Match( _
Target.Value, Range("Categories"), 0)
if iserror(res) then
Application.EnableEvents = False
Target.ClearContents
end if
End if
Found:
Application.EnableEvents = True

End Sub

--
Regards,
Tom Ogilvy


"Steven" wrote in message
...
I need help with the following code. The following code works except that
if
the cell already has a valid value and the user types in a new valid value
it
reverts back to the original value that was in the cell. It only accepts
the
new valid value if the cell is blank or if the new value is picked off the
dropdown of the Data Validation / List .... dropdown list. But I need
it
to also work if the user types in a new valid value over an already
existing
valid value ; I want it to keep the new valid value then also.

-----------------------------------------------------------------------------------------

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo NotFound:
If ActiveCell.Column = 3 Then
With Target
.Value = Application.WorksheetFunction.VLookup( _
.Value, Range("Categories"), 1, False)
End With
End If

GoTo Found:
NotFound:
Target.Value = ""

Found:
Application.EnableEvents = True

End Sub

-------------------------------------------------------------------------------------------

Thank you for your help.

Steven