vba 如何使用 FormatConditions 根据单元格值与其他单元格的比较来更改单元格颜色？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10327586/
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 use FormatConditions to change the cell color based on the compare of cell value with other cell?
提问by David Kin
I need to change the cell color if the cell value is bigger than another cell in other column. for example value in G6 > D6, and this rule need to apply to the whole column.
如果单元格值大于其他列中的另一个单元格，我需要更改单元格颜色。例如 G6 > D6 中的值，此规则需要应用于整列。
I used formatConditions implemented some code, but the result is not very correct.
我用formatConditions 实现了一些代码，但结果不是很正确。
Set rngCell = Cells(6, 7)
Set objCF = rngCell.FormatConditions.Add _
(Type:=xlCellValue, Operator:=xlGreater, Formula1:=rngCell.offset(, -3))
'set formats for new CF
With objCF
.Font.ColorIndex = 26
.Interior.ColorIndex = 19
End With
With this code , I got the result rule is: Cell Value > 18 (18 is the cell value of D6)
用这段代码，我得到的结果规则是：单元格值> 18（18是D6的单元格值）
But what I want is rule like: Cell value > $D6
但我想要的是这样的规则：单元格值 > $D6
Anyone can help?
任何人都可以帮忙吗？
采纳答案by Zairja
This is the method I used (you can easily create and modify one using the Macro Recorder). The formatting will be applied to the seventh column ("G"). The formula is self-explanatory. Note that since the formula is a string you can concatenate the columns / rows dynamically.
这是我使用的方法（您可以使用宏记录器轻松创建和修改一个）。格式将应用于第七列（“G”）。该公式是不言自明的。请注意，由于公式是一个字符串，您可以动态连接列/行。
Dim r As Range
Set r = Sheet1.Columns(7)
r.FormatConditions.Add Type:=xlExpression, Formula1:="=$G1>$D1"
r.FormatConditions(r.FormatConditions.Count).SetFirstPriority
With r.FormatConditions(1)
.Interior.PatternColorIndex = xlAutomatic
.Interior.ColorIndex = 19
.Font.ColorIndex = 26
End With
r.FormatConditions(1).StopIfTrue = False
set r = nothing
回答by David Kin
Thanks all for the input, maybe I didn't describe my question correctly. what I want is only change the color of one cell in column G, for instance the value $G$9 > $D$9, then I only need to change the format of cell G9, the same rule for the whole column(exclude the four head rows).
感谢大家的投入，也许我没有正确描述我的问题。我想要的只是改变 G 列中一个单元格的颜色，例如值 $G$9 > $D$9，那么我只需要更改单元格 G9 的格式，整个列的规则相同（排除四个头行）。
Now I have worked out one solution, currently it works fine.
现在我已经制定了一个解决方案，目前它工作正常。
Dim r As Range
Set r = Cells(5, 7)
r.FormatConditions.Delete
r.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=$D5"
r.FormatConditions(r.FormatConditions.Count).SetFirstPriority
With r.FormatConditions(1)
.Interior.PatternColorIndex = xlAutomatic
.Interior.ColorIndex = 19
.Font.ColorIndex = 26
End With
r.FormatConditions(1).StopIfTrue = False
r.Copy
Range("G5:G" & lastRowNum).PasteSpecial xlPasteFormats
回答by Siddharth Rout
If you are doing if for just that cell then try this
如果你只是为那个单元做 if 那么试试这个
Set rngCell = Cells(6, 7)
Set objCF = rngCell.FormatConditions.Add(Type:=xlExpression, _
Operator:=xlGreater, _
Formula1:="=" & rngCell.Address & ">" & rngCell.Offset(, -3).Address)
'set formats for new CF
With objCF
.Font.ColorIndex = 26
.Interior.ColorIndex = 19
End With
If you are doing it for the entire column then try this
如果您正在为整个列执行此操作，请尝试此操作
Set rngCell = Cells(1, 7)
Set objCF = Columns("G:G").FormatConditions.Add(Type:=xlExpression, _
Operator:=xlGreater, _
Formula1:="=" & Replace(rngCell.Address, "$", "") & _
">" & Replace(rngCell.Offset(, -3).Address, "$", ""))
'set formats for new CF
With objCF
.Font.ColorIndex = 26
.Interior.ColorIndex = 19
End With
