In addition to Ron's "worked perfectly" solution.

If your needs changed to many of these items, you can add to the "nums" and
"vals" arrays and not use the many "Case is" statements.

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A:A") 'adjust to suit
If Intersect(Target, r) Is Nothing Then
Exit Sub
On Error GoTo endit:
Application.EnableEvents = False
End If
nums = Array(400, 401, 402, 403) 'adjust to suit
vals = Array("James", "Mike", "Harold", "Oscar") 'adjust to suit
For Each rr In r
ival = 0
For i = LBound(nums) To UBound(nums)
If rr.Value = nums(i) Then
ival = vals(i)
End If
Next
If ival 0 Then
rr.Value = ival
End If
Next
endit:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

On Tue, 13 May 2008 09:58:01 -0700, Carolan
wrote:

Thanks everyone - Ron's solution worked perfectly for my needs.