Thank you Julie!! Thats brilliant!

Jo

"JulieD" wrote:

Hi Jo

then you need to use a VBA solution, here's one way ...

first of all you still do the first few steps of Debra's reply:
Select cell A1
Type the formula: =IF(B1="","",l)
Format the cell with Wingding font

then the following code pasted into the "sheet module" of the sheet will do
the conditional formatting for you - in this example i've used 9 conditions
....
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 -
assuming you want the conditional formatting to work on cell A1 based on the
value in B1
'--code start
Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Range("B1")) Is Nothing Then
With Target
Select Case .Value
Case "VH": Range("A1").Font.ColorIndex = 4
Case "BC": Range("A1").Font.ColorIndex = 3
Case "XY": Range("A1").Font.ColorIndex = 0
Case "AA": Range("A1").Font.ColorIndex = 6
Case "AB": Range("A1").Font.ColorIndex = 13
Case "AC": Range("A1").Font.ColorIndex = 46
Case "AD": Range("A1").Font.ColorIndex = 11
Case "AE": Range("A1").Font.ColorIndex = 7
Case "AF": Range("A1").Font.ColorIndex = 55
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub
'---code end

Hope this helps
Cheers
JulieD

"Jo" wrote in message
...
Thanks Debra! That works superbly! But what if there are more than 4
colours
required?

Jo