automatic color change in cells using a drop down list

How do I get the colors to change automatically when I use a drop down list.
Per say here is what I am trying to do

If a certain "word" is used from the drop down list I want the background of
that cell to change its color to "green." And if later I change the "word" to
another from the drop down list, it will change it's color to a specified
color.

The drop down list that I use was created from cells that have the colors
already in the "word", but I do not know how to make the list show the colors
so it puts the word & color automatically in the drop down list to the cell
with the drop down (if that makes any sense).

If there is a way, please spell it out simple enough for me to understand,
as I don't know fully the capabilities of formulas or vba.

ken

You could use Conditional Formatting to color the cell based on the "word"
entered.

However this gives you 4 choices only, including the default "no color".

For more you would need VBA event code.

Example below. Adjust range and A, B, C etc. to suit.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim rng As Range
Dim vRngInput As Variant
Set vRngInput = Intersect(Target, Range("A1"))
If vRngInput Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
For Each rng In vRngInput
'Determine the color
Select Case rng.Value
Case Is = "A": Num = 10 'green
Case Is = "B": Num = 1 'black
Case Is = "C": Num = 5 'blue
Case Is = "D": Num = 7 'magenta
Case Is = "E": Num = 46 'orange
Case Is = "F": Num = 3 'red
End Select
'Apply the color
rng.Interior.ColorIndex = Num
Next rng
endit:
Application.EnableEvents = True
End Sub

This code wopuld be copy/pasted to a worksheet module.

Right-click on the sheet tab and "View Code" to open the module.


Gord Dibben Excel MVP

On Thu, 20 Jan 2005 20:53:01 -0800, kennethwt
wrote:

How do I get the colors to change automatically when I use a drop down list.
Per say here is what I am trying to do

If a certain "word" is used from the drop down list I want the background of
that cell to change its color to "green." And if later I change the "word" to
another from the drop down list, it will change it's color to a specified
color.

The drop down list that I use was created from cells that have the colors
already in the "word", but I do not know how to make the list show the colors
so it puts the word & color automatically in the drop down list to the cell
with the drop down (if that makes any sense).

If there is a way, please spell it out simple enough for me to understand,
as I don't know fully the capabilities of formulas or vba.