I have the code below that uses conditional formatting to add a colour to a cell depending on the number that is in the cell. When the number is -3 or below it should turn green but doesn't. When the number is between -3 and 50 it should be amber and over 50 is red. The code only seems to be printing amber and red and ignores the condition for turning the cell green.
Sub ConditionalFormatting()
lLow = -3
lHigh = 50
Set rng = Range("H5:H19, H21:H24, H26:H29, H26:H29, H31:H36, H38:H44")
rng.FormatConditions.Delete ' delete any pre-existing formatting
' add greater than condition
With rng.FormatConditions.Add(xlCellValue, xlGreater, lHigh)
.Interior.Color = rgbRed
End With
' add middle condition
With rng.FormatConditions.Add(xlCellValue, xlBetween, lHigh, lLow)
.Interior.Color = rgbGold
End With
' add less than condition
With rng.FormatConditions.Add(xlCellValue, xlLess, lLow)
.Interior.Color = rgbGreen
End With
End Sub
Bookmarks