I have a spreadsheet where I keep track of the skill level of employees.
They can select from None, Beginner, Intermediate, Advanced and Expert. I
would like the text color to be different for each selection so that when I
look at the results if i am trying to find all those that have indicated
expert for a particular product I just need to look for the color.

i tried using conditional formatting but it only gives you 3 conditions and
when I tried to copy it for each person, the formula would. Hen even when I
tried changing another column manually it kept changing back to the original
formula I setup.

Thanks

Right-click on the sheet tab and "View Code"

Copy/paste the code below into that module.

Edit the range to suit.

Alt + q to return to Excel.


Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<=== change to suit

check_words = Array("advanced", "expert", "beginner", _
"none", "intermediate")
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
For i = LBound(check_words) To UBound(check_words)
If InStr(1, .Value, check_words(i)) Then
Select Case i + 1
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
Case 5: .Interior.ColorIndex = 17 'periwinkle
End Select
End If
Next i
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP


On Wed, 1 Jul 2009 11:30:02 -0700, Devo
wrote:

I have a spreadsheet where I keep track of the skill level of employees.
They can select from None, Beginner, Intermediate, Advanced and Expert. I
would like the text color to be different for each selection so that when I
look at the results if i am trying to find all those that have indicated
expert for a particular product I just need to look for the color.

i tried using conditional formatting but it only gives you 3 conditions and
when I tried to copy it for each person, the formula would. Hen even when I
tried changing another column manually it kept changing back to the original
formula I setup.

Thanks

Thanks, it worked great.

Two other questions I thought you may be able to answer.

Where do you find the color index numbers? I have tried searching for it
and couldn't find anything.

Also, is there a way to change the code so that you change the color of the
text rather than of the cell?