Here's a little ditty that someone in the group gave me awhile
back.........perhaps you can modify it to fit your needs.........

Private Sub Worksheet_Change(ByVal Target As Range)
' this has to go in your worksheet module:
' right-click on your tab name and choose 'code' in the context menu
' paste your code in the appearing editor window
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1:A5")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
Select Case .Value
Case Is = Range("e1").Value: .Interior.ColorIndex = 4 'green
Case Is = Range("e2").Value: .Interior.ColorIndex = 5 'dark blue
Case Is = Range("e3").Value: .Interior.ColorIndex = 6 'yellow
Case Is = Range("e4").Value: .Interior.ColorIndex = 7 'magenta
Case Is = Range("e5").Value: .Interior.ColorIndex = 8 'light blue
End Select
End With
CleanUp:
Application.EnableEvents = True
End Sub

Vaya con Dios,
Chuck, CABGx3



"Also" wrote:

Had a good search but have to ask sadly.

Using a Macro I have generated several columns of data.
For columns F, H, I and J I want to in effect conditional format based on 4
conditions- the 3 limit stumps me!

The idea is that all cells in range which have "1st" are on colour (with
different text colour), "2nd" and so on- BUT there is more than just "1st"
etc in the cell.

Using conditional formatting I've got the result below, but of course can't
add a 4th! Any ideas/help please.
I've self taught myself Macros but I can only go so far.

' F Column
Columns("F:F").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=ISNUMBER(SEARCH(""1st"",$F1))"
Selection.FormatConditions(1).Font.ColorIndex = 2
Selection.FormatConditions(1).Interior.ColorIndex = 10
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=ISNUMBER(SEARCH(""2nd"",$F1))"
Selection.FormatConditions(2).Interior.ColorIndex = 43
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=ISNUMBER(SEARCH(""3rd"",$F1))"
Selection.FormatConditions(3).Font.ColorIndex = xlAutomatic
Selection.FormatConditions(3).Interior.ColorIndex = 45