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

Hi,
Thank you so much. Above piece of code was really helpful to me.
Is it okay with your If I ask for your email id? If yes kindly pass on
the same.

Regards,
Swarna Kumar T.S

For what purpose would you need my email address?


Gord

On Thu, 7 Feb 2008 06:09:49 -0800 (PST), wrote:

Hi,
Thank you so much. Above piece of code was really helpful to me.
Is it okay with your If I ask for your email id? If yes kindly pass on
the same.

Regards,
Swarna Kumar T.S

Hi,
Also can we make the above code to be generic engouh so that I
need not replicate the same functionality for several work sheets
(sheet1, sheet2, sheet3 etc...) of an excel file. One more thing is
that ....in one sheet say sheet1, if I had two columns in which I need
to apply the color index then...I have replicated your code twice
applying for two different ranges. Can this be optimized?

So in a nutshell....can we have the above piece of code applied
to all the sheets of an excel file cum multiple ranges in a particular
sheet or sheets?

thanks,
Swarna Kumar T.S

Hi,
Just to build a friendly relationship...

thanks,
Swarna Kumar T.S

Depends upon what you want to do in each worksheet.

If all ranges and cases are equal in each sheet, you can add the code once to
Thisworkbook module and have no code in each sheet.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
code goes here and works on any sheet
End Sub

If ranges and/or cases are different on each sheet, code will go into individual
sheets.

The original code is restricted to "A1". Below is new code to cover a couple of
distinct ranges on a sheet.

BTW, I would recommend using this code instead of the original I posted. I
noticed a glaring flaw in that code and should be dumped.

Private Sub Worksheet_Change(ByVal Target As Range)
Const My_Range As String = "A1:A10, C1:C34" 'adjust as required

On Error GoTo endit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(My_Range)) Is Nothing Then
With Target
Select Case .Value
Case Is = "a": .Font.ColorIndex = 3 'red
Case Is = "b": .Font.ColorIndex = 46 'orange
Case Is = "c": .Font.ColorIndex = 10 'green
Case Is = "d": .Font.ColorIndex = 5 'blue
End Select
End With
End If

endit
Application.EnableEvents = True
End Sub


Gord

On Thu, 7 Feb 2008 09:14:08 -0800 (PST), wrote:

Hi,
Also can we make the above code to be generic engouh so that I
need not replicate the same functionality for several work sheets
(sheet1, sheet2, sheet3 etc...) of an excel file. One more thing is
that ....in one sheet say sheet1, if I had two columns in which I need
to apply the color index then...I have replicated your code twice
applying for two different ranges. Can this be optimized?

So in a nutshell....can we have the above piece of code applied
to all the sheets of an excel file cum multiple ranges in a particular
sheet or sheets?

thanks,
Swarna Kumar T.S

Let's just remain news group pals rather than pen pals.


Gord

On Thu, 7 Feb 2008 09:16:09 -0800 (PST), wrote:

Hi,
Just to build a friendly relationship...

thanks,
Swarna Kumar T.S

I have a list of 5 holidays. On selection, I want to change to different color for each of them. I tried using the codes below but it gives me error in VBA. Can you help? It is very urgent...