Adjust this code to suit your example below.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim rng As Range
Dim vRngInput As Variant
Set vRngInput = 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 UCase(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
Case is = "G": Num = 8 'cyan
End Select
'Apply the color
rng.Interior.ColorIndex = Num
Next rng
endit:
Application.EnableEvents = True
End Sub

To see the colrindex numbers visit David McRitchie's site.

http://www.mvps.org/dmcritchie/excel...s.htm#dpalette

The above is sheet event code. Right-click on the sheet tab and "View Code".
Copy/paste the code into that sheet module. Alt + q to return to Excel window.


Gord

On Mon, 4 Feb 2008 10:41:10 -0800 (PST), wrote:

hi,
By using your approach, I could add only three conditions whereas
my drop down has 7 values in it and I want one color scheme for each
drop down value.

Say the choices as

1) Open -- to be displayed in "Red" color
2) Re-Open -- in "Orange" color
3) Fixed -- in "Blue" color
4) Work-In-Progress -- in "Light Blue" Color
5) Closed -- in "Green" Color
6) Hold -- in "Teal" color
7) Unable-To-Test -- in "Cyan" color

Shall wait for your valuable inputs/suggestions in the above
regards.
thanks,
Swarna Kumar T.S