Thanks Don

Missed OTHER post, I guess.


Gord

On Thu, 25 Jun 2009 07:13:18 -0500, "Don Guillett"
wrote:

Gord. OP using a drop down so, in the OTHER post, I suggested this vlookup
instead.

I found your drop down list and named the range colorpicks. I also named the
validation range to colorpicksA so you may now move it as I did , even to
another sheet, and it will still work because it is assigned to the named
range. Goto the table and change the color numbers as desired. I have also
attached colorpalette so you can see the colors. You had validation assigned
to the whole column. Do NOT do that. Now when you click on col M (if
validation there) the color will change according to the vlookup table.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < Columns("m").Column Then Exit Sub
Cells(Target.Row, 1).Resize(, 13).Interior.ColorIndex = _
Application.VLookup(Target, Range("colorpicks"), 2, 0)
End Sub