Hi

couple of options - there's a conditional formatting add-in available for
download at
http://www.xldynamic.com/source/xld.....Download.html

or you can use VBA in a worksheet_change event e.g.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Range("B6:B10")) Is Nothing Then
With Target
Select Case .Value
Case 1: Target.Font.ColorIndex = 4
Case 2: Target.Font.ColorIndex = 3
Case 3: Target.Font.ColorIndex = 0
Case 4: Target.Font.ColorIndex = 6
Case 5: Target.Font.ColorIndex = 13
Case 6: Target.Font.ColorIndex = 46
Case 7: Target.Font.ColorIndex = 11
Case 8: Target.Font.ColorIndex = 7
Case 9: Target.Font.ColorIndex = 55
End Select
End With
End If

ws_exit:
Application.EnableEvents = True

End Sub

--- this turns the font of cells in the range B6:B10 a different colour
depending on what value (between 1 & 9) is entered in the cell.

to use the code, right mouse click on the sheet tab of the sheet where you
want the code to run and choose view code - copy and paste the above in
changing B6:B10 to your range and the numbers 1 through 9 to your criteria
(and, of course the colorindex values to what you want).

Hope this helps

Cheers

JulieD



"chetwyndthomas" wrote in message
...

I want to create a themal analysis problem whereby the results of the
calculation are displayed in a grid of cells (representing the shape of
the
item). I then want to apply conditional formatting which colours the cells
depending on the value. I want there to be 10 colours ranges. I can do
this
easily for four colours using standard conditional formatting but I want
to
use 10 colours, is there a way to expand the conditional formatting?