Changing cell color based on value in another cell

[Patrick C. Simonds]

I have a worksheet with 941 rows. I need a macro that will look at each row
and if the value in of the cell in column Z of that row = 2 it should format
the cell in column C Yellow, if the cell in column Z of that row = 3 it
should format the cell to Red, and if the cell in column Z of that row is
grater than 3 it should format the cell to Grey with white text. If the Cell
in column Z is equal to 1 or less the cell in column C should be the normal
format which has been set for it.

I know this can be done easily with conditional formatting in Office 2007,
but I am doing this to be used with Office 2003 and with the other
conditional formats I have in place, I have exceeded the 3 conditional
format limit.

 

[Stephen Newman]

On Mon, 21 Jan 2008 14:49:01 -0800, "Patrick C. Simonds"

In the worksheet properties:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim X As Long
For X = ActiveSheet.Cells(Rows.Count, "D").End(xlUp).Row To 1 Step
-1
If ActiveSheet.Cells(X, "Z").Value = 2 Then
With ActiveSheet.Cells(X, "C").Interior
.ColorIndex = 6
End With

ElseIf ActiveSheet.Cells(X, "Z").Value = 3 Then
With ActiveSheet.Cells(X, "C").Interior
.ColorIndex = 3
End With

ElseIf ActiveSheet.Cells(X, "Z").Value > 3 Then
With ActiveSheet.Cells(X, "C").Font
.ColorIndex = 2
End With
With ActiveSheet.Cells(X, "C").Interior
.ColorIndex = 16
End With
End If
Next
End Sub

 

[Rumplestiltskin]

On Mon, 21 Jan 2008 14:49:01 -0800, "Patrick C. Simonds"

Correction

In the worksheet properties:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim X As Long
For X = ActiveSheet.Cells(Rows.Count, "Z").End(xlUp).Row To 1 Step
-1
If ActiveSheet.Cells(X, "Z").Value = 2 Then
With ActiveSheet.Cells(X, "C").Interior
.ColorIndex = 6
End With

ElseIf ActiveSheet.Cells(X, "Z").Value = 3 Then
With ActiveSheet.Cells(X, "C").Interior
.ColorIndex = 3
End With

ElseIf ActiveSheet.Cells(X, "Z").Value > 3 Then
With ActiveSheet.Cells(X, "C").Font
.ColorIndex = 2
End With
With ActiveSheet.Cells(X, "C").Interior
.ColorIndex = 16
End With
End If
Next
End Sub

Sorry. I used "D" for testing as "Z" was off my screen.