使用 VBA 进行条件格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9004712/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Conditional Formatting using VBA
提问by Ravi Vasi
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:
我想要使用条件格式的正确代码。我有 4 个季度销售表 (“K8:K207”) 的总和数据。我想在有 3 个条件的地方应用条件格式：
- Highlight Column K(Total Yearly Sale) for the year greater than 1,00,000 as Green
- between 90,000 to 1,00,000 as amber
- and less than 90,000 as red
- 将大于 1,00,000 的年份的 K 列（年度总销售额）突出显示为绿色
- 琥珀色介于 90,000 至 1,00,000 之间
- 并且少于 90,000 为红色
Please help me how I can write a code using loop.
请帮助我如何使用循环编写代码。
回答by mischab1
You don't need a loop for this. You can just add a new FormatCondition to your range object.
您不需要为此循环。您只需向范围对象添加一个新的 FormatCondition 即可。
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
