Hi Dave
here's some code which shows you how to apply up to 9 coditional formats
using VBA
this code needs to be pasted into the "sheet module" of the sheet where you
want the formats to be applied - 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 B6
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Range("B6")) Is Nothing Then
With Target
Select Case .Value
Case 1: Range("B6").Font.ColorIndex = 4
Case 2: Range("B6").Font.ColorIndex = 3
Case 3: Range("B6").Font.ColorIndex = 0
Case 4: Range("B6").Font.ColorIndex = 6
Case 5: Range("B6").Font.ColorIndex = 13
Case 6: Range("B6").Font.ColorIndex = 46
Case 7: Range("B6").Font.ColorIndex = 11
Case 8: Range("B6").Font.ColorIndex = 7
Case 9: Range("B6").Font.ColorIndex = 55
End Select
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
--- this turns the font of B6 a different colour depending on what value
(between 1 & 9) is entered in the cell.
if you'ld like additonal help with your criteria & formatting statements,
please feel free to post back with more details.
Cheers
JulieD