Conditional Formatting using VBA

Question

I would like the right code for using conditional formatting. I have data for sum of 4 Quarter sales form ("K8:K207"). I want to apply conditional formatting where I have 3 conditions:

1. Highlight Column K(Total Yearly Sale) for the year greater than 1,00,000 as Green
2. between 90,000 to 1,00,000 as amber
3. and less than 90,000 as red

Please help me how I can write a code using loop.

Answer 1

You don't need a loop for this. You can just add a new FormatCondition to your range object.

lLow = 90000
lHigh = 100000

Set rng = Range("K8:K207")
rng.FormatConditions.Delete  ' delete any pre-existing formatting

' add greater than condition
With rng.FormatConditions.Add(Type:=xlCellValue, Operator:=xlGreater, Formula1:="=" & lHigh)
   .Interior.Color = rgbLimeGreen
End With

' add middle condition
With rng.FormatConditions.Add(Type:=xlCellValue, Operator:=xlBetween, Formula1:="=" & lLow, Formula2:="=" & lHigh)
   .Interior.Color = rgbGold
End With

' add less than condition
With rng.FormatConditions.Add(Type:=xlCellValue, Operator:=xlLess, Formula1:="=" & lLow)
   .Interior.Color = rgbRed
End With