Without details on desired colors vs values here is a simple sample demo of
how to change the interior color of B1 based upon the value in A1:

Sub Macro1()
Range("B1").Interior.ColorIndex = Range("A1").Value
End Sub

1. enter the macro
2. put a number in A1 (like 36)
3. run the macro


The sample can easily be modified to handle alternative mappings and font
colors as well.
--
Gary's Student


"Simon Jefford" wrote:

I would like to format a cell based upon the contents of another cell. The
problem is that the determining cell has five variations - hence I cannot use
conditional formatting as this only allows three variations. In total, on a
worksheet, I have about 160 cells whose format I wish to change based upon
the contents of 160 associated cells on another worksheet. When changing the
format, I wish to change forground and background colours.
Any help would be much appreciated.
Thanks, Simon