CODEWITHSUNDEEP
excelconditional-formattingvba
Pricey
Pricey

Reputation: 5939

Conditional formatting does not seem to be applied after updating cells

is there a way to refresh a cells conditional formatting using 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.

I update the values using VBA and then do

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.

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?

Upvotes: 1

Views: 10279

Answers (3)

manotheshark
manotheshark

Reputation: 4357

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.

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

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

Upvotes: 0

bik128
bik128

Reputation: 11

You can also try this: 

Sub refreshScreen()
  Application.Parent.Visible = False
  Application.Parent.Visible = True
End Sub

Upvotes: 1

Pricey
Pricey

Reputation: 5939

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.

Range("A1").Formula = "=B1"
Range("A1").Select
Range("A1").Activate

Upvotes: 1

Related Questions