drop down list that changes cell color upon selection

J

Jay

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.
 
G

Gord Dibben

Select a cell then Data>Validation>Allow>List.

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

Format>Conditional Formatting>Condition1>Cell 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
 
S

swarnakumar.ts

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
 
S

swarnakumar.ts

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
 
G

Gord Dibben

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/colors.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
 
S

swarnakumar.ts

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
 
G

Gord Dibben

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
 
S

swarnakumar.ts

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
 
S

swarnakumar.ts

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
 
G

Gord Dibben

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
 
R

Ritesh Mehta

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...
 
Top