How do I get the font color to change according to the selection .

G

Greg

I have a drop down cell that others to allow others to select one of 9
options. I want thier selection to be color coded. If they select 1 then I
want it displayed as green, 2 to be displayed as red, 3 to be displayed as
black, 4 to be displayed as yellow, and so on.
 
J

JulieD

Hi Greg

as there's more than 3 conditions you can't use conditional formatting,
however the following code pasted into the "sheet module" of the sheet
you've got the drop down list on on should give you what you're after - this
assumes that the drop down list on cell B6.

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Range("B6")) Is Nothing Then
With Target
Select Case .Value
Case 1: Range("B6").Font.ColorIndex = 4
Case 2: Range("B6").Font.ColorIndex = 3
Case 3: Range("B6").Font.ColorIndex = 0
Case 4: Range("B6").Font.ColorIndex = 6
Case 5: Range("B6").Font.ColorIndex = 13
Case 6: Range("B6").Font.ColorIndex = 46
Case 7: Range("B6").Font.ColorIndex = 11
Case 8: Range("B6").Font.ColorIndex = 7
Case 9: Range("B6").Font.ColorIndex = 55
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Hope this helps
Cheers
JulieD
 
J

Jason Morin

If you're using Validation and the drop-down is in A1,
right-click on the worksheet tab, View Code, and paste in:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng As Range
Dim r As Long

Set rng = Range("A1")
If Intersect(Target, rng) Is Nothing Then Exit Sub
With Application
.ScreenUpdating = False
r = .WorksheetFunction. _
Match(rng.Value, Range("G1:G5"), 0)
rng.Font.ColorIndex = Cells(r, "G").Font.ColorIndex
.ScreenUpdating = True
End With
End Sub

--
This assumes the source list for the validation is in
G1:G5 and you've colored each item in G1:G5 to the way
you want it to appear in A1.

HTH
Jason
Atlanta, GA
 
G

Greg

JulieD,

I'm not the worlds greatest at Excel. When you told me to paste the attached
in to the "Sheet Module", what is the sheet module?
 
J

JulieD

Hi Greg

sorry, it's hard to guess how much detail to go into on the first post
right mouse click on the sheet tab and choose view / code
you should see on the top left of the VBE window your file name in bold (if
not try view / project explorer)
and the sheet that you were on selected ... that's the "sheet module" ... if
the wrong sheet is selected then just double click on the correct one
on the right you should see some white space - copy & paste the code in
there


hope this helps
Cheers
JulieD
 
G

Greg

JulieD,

I feel like a nut. I went in and set up the sheet as you told me too, but it
still isn't changing the color. I even set up a sheet with the example that
you gave and couldn't get it to work. Is there a way that I can get you to
review my sheet and tell me where I goofing this up?

Thanks,
Greg
 
J

JulieD

Hi Greg

email it to me direct (julied_ng @ hcts dot net dot au) its 2am here so i
should be going to bed in the next 1/2 hr or so ... if its not here by then
i'll look at it tomorrow.

Cheers
JulieD
 
G

Greg

Jason,

How would your formula change if I used B9 for the validation cell and the
range was B237:B250? I changed those values, but couldn't get it to work.
 
Top