Which version of Excel?

If earlier than 2007 you would need some code or use Bob Phillips' CFPlus
add-in.

http://www.xldynamic.com/source/xld.....Download.html

Some event code...................

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:A20" '<=== change to suit
check_words = Array("Unacceptable", "Below", "Meets", "Exceeds")
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
End Select
GoTo ws_exit
End If
Next i
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP



On Tue, 7 Oct 2008 10:01:03 -0700, MT wrote:

How do I go about formatting if I have more than 3 different conditions?
For example, text =
"Exceeds": Green
"Meets": Blue
"Below": Yellow
"Unacceptable": Red

thanks in advance for your help