Conditional Formatting VBA help

[ashley1984]

Hi All,

I’m trying to apply conditional formatting to a table using VBA. I’ve currently got this set up as conditional formatting through Excel but the file goes off to users who change/delete the conditional formatting, so I want a macro to reset the conditional formatting once I get the file back.

I want the £ Sales YOY % Change & Vol YoY % Change rows to be green or red if they are above or below 0

The data table is below

and the code I'm having trouble with is:

Sub ConditionalFormatTable

If Cells("D5:L5").Value < 0 Then
Cells("D5:L5").Interior.Color = RGB(255, 199, 206)
Else
Cells("D5:L5").Interior.Color = RGB(198, 239, 206)
End If

End Sub

 

[Fluff]

You cannot check an entire range of values like that, you will need to loop through the range.