Conditional formatting using VBA

Question

I need the right code to use conditional formatting. I have data for the amount of 4 quarter sales ("K8: K207"). I want to apply conditional formatting where I have 3 conditions:

• Highlight column K (total annual sales) for a year in excess of 1.00,000 as green
• 90,000 to 1,000,000 as amber
• and less than 90,000 like red

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

Answer 1

You do not need a loop for this. You can simply 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