Why not load the alternate automatically

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_COLOUR_RANGE As String = "A1"
Const WS_COLOUR_NUM_RANGE As String = "B1"
Dim tmp

On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If Not Intersect(Target, Me.Range(WS_COLOUR_RANGE)) Is Nothing Then
On Error Resume Next
tmp = Application.VLookup(.Value, Range("colours"), 2, False)
On Error GoTo 0
If Not IsError(tmp) Then
Me.Range(WS_COLOUR_NUM_RANGE).Value = tmp
End If
ElseIf Not Intersect(Target, Me.Range(WS_COLOUR_NUM_RANGE)) Is
Nothing Then
On Error Resume Next
tmp = Application.Index(Range("colours").Columns(1), _
Application.Match(.Value, Range("colours").Columns(2),
0))
On Error GoTo 0
If Not IsError(tmp) Then
Me.Range(WS_COLOUR_RANGE).Value = tmp
End If
End If

End With

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"BorisS" wrote in message
...
I have two lists, one of color names, the other of color numbers. I need
two
cells, each of which has one of these values, to cross reference
themselves,
to only allow a matching color-number combination, and the cells need to
be
dropdowns. I am guessing I need to do this with validation, but don't
know
how to code the validation lists.

In other words, if a user selects from the color dropdown, the color
number
dropdown needs become limited to only the matching number. If the user
selects the number, then the color name dropdown needs to limit to only
the
color name. There is a one to one match between the colors and numbers,
and
they'll be in a list side by side, for reference of the validation.

Thx for any help.
--
Boris