You can use the change event in the sheet module then

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Range("B1").Value
Case 0: Range("A1:A4000").Interior.ColorIndex = 3
Case 1: Range("A1:A4000").Interior.ColorIndex = 5
Case 2: Range("A1:A4000").Interior.ColorIndex = 7
Case 3: Range("A1:A4000").Interior.ColorIndex = 9
Case 4: Range("A1:A4000").Interior.ColorIndex = 11
Case 5: Range("A1:A4000").Interior.ColorIndex = 13
Case Else: Range("A1:A4000").Interior.ColorIndex = xlColorIndexNone
End Select
End Sub


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"brownti via OfficeKB.com" <u31540@uwe wrote in message news:6e7e49cc53550@uwe...
I need to use six different colors...

Ron de Bruin wrote:
Hi brownti

You can use CF

See
http://www.contextures.com/xlCondFormat01.html

I need to create a macro that will display a color in a cell depending on the
condition of a different cell. For example if cell B1 has a 0 in it, A1 will
turn red, otherwise it wont have a color. This needs to happen in about 4000
consecutive cells. Probably be easiest if it just happens when the workbook
is opened. Thanks in advance for any help.

--
Message posted via http://www.officekb.com