Changing cell color based on a value in another cell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ordnance1 - 23 Jan 2008 00:07 GMT

I need a macro which will look at each row and if the cell in column Z =2
then the cell in column C should be formatted Yellow, if it = 3 the cell will
be formatted Red and if it is greater than 3 the cell will be formatted
Grey. There are 941 rows startimng at row 7. I know this can be done as a
conditional format, but this is for use with Office 2003 and because of other
conditional formats I am using I have exceeded the three allowed conditional
formats

Reply to this Message

ryguy7272 - 23 Jan 2008 20:09 GMT

This should do what you want:

Sub ColorCells()
Dim rCell As Range
Dim nCount As Long
With Range("Z7:Z100")
.Interior.ColorIndex = xlColorIndexNone
For Each rCell In .Cells
If rCell = "2" Then
rCell.Offset(0, -23).Interior.ColorIndex = 6
End If
If rCell = "3" Then
rCell.Offset(0, -23).Interior.ColorIndex = 3
End If
If rCell > "3" Then
rCell.Offset(0, -23).Interior.ColorIndex = 15
End If

Next rCell
End With
End Sub

Regards,
Ryan--

Signature

RyGuy

> I need a macro which will look at each row and if the cell in column Z =2
> then the cell in column C should be formatted Yellow, if it = 3 the cell will
[quoted text clipped - 3 lines]
> conditional formats I am using I have exceeded the three allowed conditional
> formats

Reply to this Message