Conditional Formatting....

C

Chris

I want to do more than three conditional formats....the condition is if the
word 'deliver' exists the color should be green....if the word 'tank set'
exists the color should be off white....I have about twenty words with twenty
different colors...any suggestions. Thanks Chris
 
T

Toppers

Try:

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

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

check_words = Array("word", "tank set") '<=== set to your words/phrases
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
 
G

Gary''s Student

You should use a macro to paint the colors rather than Conditional Formatting.
 
Top