公式中的 Excel VBA 条件格式公式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26678211/
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
Excel VBA Conditional Formatting formula within formula
提问by Jay
I am stuck and I don't know if there are any solutions to this as I have tried many and kept failing.
我被卡住了，我不知道是否有任何解决方案，因为我尝试了很多并且一直失败。
I am trying to add a conditional formatting so cells in column B turns yellow if it is equal or less than 19% of it's partner cell in column A. So in the example above, cell B1 should turn yellow since $19,000 is less than or equal to 19% of $100,000.
我正在尝试添加条件格式，因此如果 B 列中的单元格等于或小于 A 列中伙伴单元格的 19%，则该单元格会变为黄色。因此，在上面的示例中，单元格 B1 应变为黄色，因为 $19,000 小于或等于至 100,000 美元的 19%。
I need to add this conditional formatting through excel vba. I tried adding the vba code below but the conditional formatting formula for all of the cells in B1:B3 get's stuck with $A1*0.19. I need B1 conditional formatting formula to be $A1*0.19, then B2 conditional formatting formula would be $A2*0.19so on and so fort. I have about 350 rows by the way not just 3. Even so, my vba code becomes $A521325*0.19or something way off the real or actual.
我需要通过 excel vba 添加这个条件格式。我尝试添加下面的 vba 代码，但 B1:B3 中所有单元格的条件格式公式都被$A1*0.19. 我需要 B1 条件格式公式为$A1*0.19，然后 B2 条件格式公式将$A2*0.19如此等等。顺便说一下，我有大约 350 行，而不仅仅是 3 行。即便如此，我的 vba 代码$A521325*0.19还是变得与真实或实际相差甚远。
With Sheet1.Range(Sheet1.Cells(1, 2), Sheet1.Cells(3, 2))
daformula = "=$A1*0.19"
.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, Formula1:=daformula
.FormatConditions(.FormatConditions.Count).SetFirstPriority
.FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
.FormatConditions(1).Interior.ThemeColor = xlThemeColorAccent2
.FormatConditions(1).Interior.TintAndShade = -0.249946592608417
.FormatConditions(1).StopIfTrue = False
End With
The idea is after running the macro which adds the conditional formatting to the sheet, when the user changes one of the cells in column B the color either disappear or reappear depending on the value the user changed the cell into (the conditional formatting must still work)
这个想法是在运行将条件格式添加到工作表的宏之后，当用户更改 B 列中的一个单元格时，颜色会消失或重新出现，具体取决于用户将单元格更改为的值（条件格式必须仍然有效） )
回答by
Try,
尝试，
With ActiveSheet.Cells(1, 2).Resize(3, 1)
.FormatConditions.Add Type:=xlExpression, Formula1:="=$B1<=($A1*0.19)"
.FormatConditions(.FormatConditions.Count).Interior.Color = 65535
End With
Whether you add other details like .SetFirstPriorityor .StopIfTruewould depend somewhat on how other CF rules may affect the same cells.
您是否添加其他细节，例如.SetFirstPriority或.StopIfTrue将在某种程度上取决于其他 CF 规则如何影响相同的单元格。
回答by Galimi
You could create an event that monitors any change in column A (place this code in the Sheet1 object)
您可以创建一个事件来监视 A 列中的任何更改（将此代码放在 Sheet1 对象中）
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
'run code to add conditional formatting
condFormat Target
End If
End Sub
Now we just have to change your above code to accept the Target and only add formatting for the equivalent cell in Column B
现在我们只需要更改上面的代码以接受目标，并且只为 B 列中的等效单元格添加格式
Public sub condFormat (Target as range)
With target.offset(0,1)
daformula = "=" & target.address & "*0.19"
.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _
Formula1:=daformula
.FormatConditions(.FormatConditions.Count).SetFirstPriority
.FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
.FormatConditions(1).Interior.ThemeColor = xlThemeColorAccent2
.FormatConditions(1).Interior.TintAndShade = -0.249946592608417
.FormatConditions(1).StopIfTrue = False
End With
end sub
I haven't accounted for changing more than 1 cell at once, but this will solve your challenge
我没有考虑一次更改超过 1 个单元格，但这将解决您的挑战
回答by Tim G.
When creating the conditional formatting from VBA, the cell selection relative to conditional formatting formula is critical.
从 VBA 创建条件格式时，与条件格式公式相关的单元格选择至关重要。
Try using ActiveSheet.cells(1,1).select if the conditional formatting formula only accesses values on the same row, or ActiveSheet.cells(2,1) if it references the value of the row above.
如果条件格式公式仅访问同一行上的值，请尝试使用 ActiveSheet.cells(1,1).select，如果它引用上面行的值，请尝试使用 ActiveSheet.cells(2,1)。
