vba 更新单元格后似乎没有应用条件格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11486732/
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 does not seem to be applied after updating cells
提问by Pricey
is there a way to refresh a cells conditional formatting using VBA?
有没有办法使用 VBA 刷新单元格条件格式？
Issue: I am in a situation where I have a cell (A1) referencing another cell (B1), which contains a =SUM() number value with the format of "number, 2 decimal places", but cell (A1) has a conditional formatting on it of "Cell Value >= 1000" and with that I am applying a custom format, otherwise it uses a Currency format for euros.
问题：我的情况是，我有一个单元格 (A1) 引用另一个单元格 (B1)，其中包含一个 =SUM() 数值，格式为“数字，小数点后 2 位”，但单元格 (A1) 有一个“单元格值> = 1000”的条件格式，并且我正在应用自定义格式，否则它使用欧元的货币格式。
I update the values using VBA and then do
我使用 VBA 更新值然后执行
Application.CalculateFull
which updates my formulae but this conditional format is only getting applied the first time the value goes over 1000... if it is less than 1000 it does not go back to its original format.
它更新了我的公式，但这种条件格式仅在值第一次超过 1000 时应用......如果它小于 1000，则不会恢复到其原始格式。
Any one had this problem before and knows how to update the conditional formatting? short of using VBA to select the cell and refresh it some how?
以前有人遇到过这个问题并且知道如何更新条件格式吗？缺少使用 VBA 选择单元格并刷新它一些如何？
采纳答案by Pricey
I have decided to select the cell , reapply the formula and then activate it each time the VBA has finished running, the below fixes my problem for now.. its just a shame its so manual.
我决定选择单元格，重新应用公式，然后在每次 VBA 完成运行时激活它，下面解决了我的问题。
Range("A1").Formula = "=B1"
Range("A1").Select
Range("A1").Activate
回答by bik128
You can also try this:
你也可以试试这个：
Sub refreshScreen()
Application.Parent.Visible = False
Application.Parent.Visible = True
End Sub
回答by manotheshark
This appears to be a bug (or inconsistency) that applies to the NumberFormat property for conditional formatting if using custom formatting. Conditional formatting will apply a custom NumberFormat, but will not revert to the default when it is no longer true. Using VBA to apply conditional formatting would work as expected when using only font/background colors, but would no longer function as expected when applying a custom NumberFormat. I worked around this by creating two conditional formats for both the true and false scenario.
如果使用自定义格式，这似乎是适用于条件格式的 NumberFormat 属性的错误（或不一致）。条件格式将应用自定义 NumberFormat，但当它不再为 true 时不会恢复为默认值。仅使用字体/背景颜色时，使用 VBA 应用条件格式将按预期工作，但在应用自定义 NumberFormat 时将不再按预期工作。我通过为 true 和 false 场景创建两种条件格式来解决这个问题。
Hide text when condition is true
条件为真时隐藏文本
.NumberFormat = ";;;"
Show text when condition is false
条件为假时显示文本
.NumberFormat = "General"
The following are set, but have no impact on behavior:
设置了以下内容，但对行为没有影响：
- EnableFormatConditionsCalculation is set to True
- EnableEvents is set to True
- Calculations are set to Automatic
- EnableFormatConditionsCalculation 设置为 True
- EnableEvents 设置为 True
- 计算设置为自动
Here is a link to the post that got me headed in the right direction:
这是一篇文章的链接，让我朝着正确的方向前进：
http://www.mrexcel.com/forum/excel-questions/735479-conditional-formatting-not-updating.html
http://www.mrexcel.com/forum/excel-questions/735479-conditional-formatting-not-updating.html
