使用VBA的条件格式 [英] Conditional Formatting using VBA

问题描述

我希望使用条件格式化的正确代码。我有4季度销售表格（K8：K207）的数据。我想应用条件格式，我有3个条件：

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. 突出显示年度大于100万的列K（年度总销售额）作为绿色


2. 在琥珀色


3. 之间的90,000到100,000，而红色

请帮助我如何使用循环编写代码。

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

推荐答案

这不需要一个循环。您可以添加一个新的FormatCondition到您的范围对象。

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

这篇关于使用VBA的条件格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！