如何对 Excel VBA 宏进行条件格式设置？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12315637/
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
How to do conditional formating an Excel VBA macro?
提问by user930679
vbaI have used the below code to insert conditional formatting in an excel cell..
vba我使用下面的代码在excel单元格中插入条件格式..
range("d" & rowno).Select
Selection.Offset(1, 0).EntireRow.Insert
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="= RC > 7"
Selection.FormatConditions(Selection.FormatConditions.count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535 'Yellow
.TintAndShade = 0
End With
The above works fine by comparing the grater than value defined which is "7"...
But if i pass variable "lhigh" in which value is stored and the same im passing it in the formaula it does not works.
e.g;
lhigh=7
通过比较大于定义的值“7”，上述工作正常...... 例如;
lhigh=7
range("d" & rowno).Select
Selection.Offset(1, 0).EntireRow.Insert
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="= RC > lhigh"
Selection.FormatConditions(Selection.FormatConditions.count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535 'Yellow
.TintAndShade = 0
End With
Please let me know how we can than calculate greater than check if we pass variable instead of direct integer value
请让我知道我们如何计算大于检查是否传递变量而不是直接整数值
回答by Jean-Fran?ois Corbett
You need this:
你需要这个：
Formula1:="= RC > " & lhigh
i.e. you need to do a string concatenation using the &operator.
即您需要使用&运算符进行字符串连接。
"= RC > " & lhighwill then evaluate as "= RC > 7".
"= RC > " & lhigh然后将评估为"= RC > 7"。
回答by DeanOC
If I name a cell "Arc" and another to "lhigh" then the following sub works for me in Excel 2007
如果我将一个单元格命名为“Arc”并将另一个单元格命名为“lhigh”，那么以下子项在 Excel 2007 中对我有用
Sub test()
Dim foo As Range
Set foo = ActiveWorkbook.Sheets("Sheet1").Range("C3")
With foo.FormatConditions _
.Add(xlExpression, Formula1:="=Arc>lhigh")
With .Font
.Bold = True
.ColorIndex = 4
End With
End With
End Sub
this will set conditional formatting on cell C3 which will kick in when the value in Arc > lhigh.
这将在单元格 C3 上设置条件格式，当 Arc > lhigh 中的值时，它将启动。
Perhaps you should simplify your code to something basic like this and then add the extra complexity. I'm guessing that your problem lies in another part of your code.
也许您应该将代码简化为类似这样的基本代码，然后添加额外的复杂性。我猜你的问题出在你代码的另一部分。
