Morning,

I have a spreadsheet in Excel 2003 and with conditional formatting only allowing for 3 formats I have had to resort to VBA to format individual areas of the sheet.

I have some code which works well, but doesn't quite do what I need.

In column R there is a calculated number. This is the number of days something has taken to be returned to me. I want my VBA to format the column J based on the corresponding cell in R.

e.g. R5 = 60 then J5 should be red
R6 = 35 then J6 should be orange

The current VBA does this, but only for the cell in R - I don't know how to to change the cell to J.

With Sheet1
        For Each rCell In .Range("R5:R1000")
            If rCell.Value > 60 Then
                rCell.Interior.ColorIndex = 3
            ElseIf rCell.Value > 30 Then
                rCell.Interior.ColorIndex = 45
            ElseIf rCell.Value <= 30 Then
                rCell.Interior.ColorIndex = xlNone
            Else
                rCell.Interior.ColorIndex = xlNone
            End If
        Next rCell
    End With