Then we do it like this

I use the whole column here but you can change it to
If Not Application.Intersect(Range("B1:B4000"), Target) Is Nothing Then



Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("B:B"), Target) Is Nothing Then
Select Case Target.Value
Case 0: Target.Offset(0, -1).Interior.ColorIndex = 3
Case 1: Target.Offset(0, -1).Interior.ColorIndex = 5
Case 2: Target.Offset(0, -1).Interior.ColorIndex = 7
Case 3: Target.Offset(0, -1).Interior.ColorIndex = 9
Case 4: Target.Offset(0, -1).Interior.ColorIndex = 11
Case 5: Target.Offset(0, -1).Interior.ColorIndex = 13
Case Else: Target.Offset(0, -1).Interior.ColorIndex = xlColorIndexNone
End Select
End If
End Sub



--

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


"brownti via OfficeKB.com" <u31540@uwe wrote in message news:6e7e8545e1aec@uwe...
I dont think I was totally clear. It more like if B1 = 0 then A1 is red, if
b2 = 0 then A1 is red etc...through 4000 rows. i think i need a loop that
checks each row individually. i dont really understand how to create loops
though.

Ron de Bruin wrote:
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

I need to use six different colors...

[quoted text clipped - 10 lines]
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