Here's my shot at it. With the drop down in F1.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 6 Then Exit Sub
If Target.Row < 1 Then Exit Sub
Target.Interior.ColorIndex = xlNone

If Range("F1").Value = "red" Then
With Target.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
Target.Font.ColorIndex = 3

ElseIf Range("F1").Value = "blue" Then
With Target.Interior
.ColorIndex = 41
.Pattern = xlSolid
End With
Target.Font.ColorIndex = 41

ElseIf Range("F1").Value = "green" Then
With Target.Interior
.ColorIndex = 4
.Pattern = xlSolid
End With
Target.Font.ColorIndex = 4

End If
End Sub

HTH
Regards,
Howard

"jjh" wrote in message
...

Hi:
Thanks for taking the time to read my question. I want to configure a
cell in Excel for color validation and I don't know how to do it. What
I want is to configure a cell with a drop-down list of colors only, so
the user can select from a set of predefined colors.
I know how to create a data validation list by placing characters in a
verticle list of cells, naming the grouping, then use data validation
using a list to provide user selection. I also know how to
conditionally format the cell based on the value chosen from the list,
e.g. if 'R' is chosen from the list, the cell color changes to red, and
the 'R' from the named list is placed in the cell.
What I want to do is choose from a list of colors and only place the
color in the cell.
I tried using non-printing ASCII characters in the list but Excel does
not accept them. I tried to color the cells that comprise the drop down
list, but the colors do not show up in the drop down list, nor when a
member of the list is chosen. All I want to do is have the user select
from a drop down list of colors only. Can anyone suggest how to do
this?
Thanks for your help
-J


--
jjh
------------------------------------------------------------------------
jjh's Profile:
http://www.excelforum.com/member.php...o&userid=36089
View this thread: http://www.excelforum.com/showthread...hreadid=559608