I would like to setup a drop down list that when the user selects a value it
will turn the cell a meaningful corresponding color...like red for high
severity...etc.

Select a cell then DataValidationAllowList.

In source enter low,medium,high OK your way out

FormatConditional FormattingCondition1Cell Value is: equal to low Format to
green pattern.

Add two more conditions for medium and high with your choice of colors.

OK your way out.


Gord Dibben MS Excel MVP

On Tue, 15 Jan 2008 13:46:03 -0800, Jay wrote:

I would like to setup a drop down list that when the user selects a value it
will turn the cell a meaningful corresponding color...like red for high
severity...etc.

hi,
By using your approach below, 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

On Jan 15, 5:44*pm, Gord Dibben <gorddibbATshawDOTca wrote:
Select a cell then *DataValidationAllowList.

In source enter *low,medium,high * OK your way out

FormatConditional FormattingCondition1Cell Value is: *equal to low *Format to
green pattern.

Add two more conditions for medium and high with your choice of colors.

OK your way out.

Gord Dibben *MS Excel MVP



On Tue, 15 Jan 2008 13:46:03 -0800, Jay wrote:
I would like to setup a drop down list that when the user selects a value it
will turn the cell a meaningful corresponding color...like red for high
severity...etc.- Hide quoted text -

- Show quoted text -

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

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

hi,
Thank you so much it indeed worked.

Is it possible to make the font color change instead of
background color of a cell in context to the above solution? Please
let know. Shall wait for your valuable inputs/suggestions in the
above

thanks,
Swarna Kumar T.S

Revised code.

Note the change from "interior.colorindex" to "Font.colorindex"

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.Font.ColorIndex = Num
Next rng
endit:
Application.EnableEvents = True
End Sub


Gord



On Wed, 6 Feb 2008 14:43:47 -0800 (PST), wrote:

hi,
Thank you so much it indeed worked.

Is it possible to make the font color change instead of
background color of a cell in context to the above solution? Please
let know. Shall wait for your valuable inputs/suggestions in the
above

thanks,
Swarna Kumar T.S