Strange Conditional Formatting Error

[Orion19]

Hi All!

I used VBA to create conditional formatting rules for a range of cells, but when I ran the macro there was no change to cells that should have been formatted. I checked to see if the code generated the rule and it did so it should have worked. What's really strange is that after checking to see if the rule was generated the conditional formatting suddenly started to work. I've tried it three times and the conditional formatting only works after manually checking to see if a rule is there (clicking "Conditional Formatting" then "Manage Rules...". I have restarted Excel thinking it was some kind of error but the situation is the same. Any ideas?

Thank you!!

Here is the rule if anyone is curious:

=AND($R29="Phase 2",$Q29>AVERAGEIF($R29:$R1529,"Phase 2",$Q29:$Q1529)+STDEV.P(IF($R29:$R1529="Phase 2",$Q29:$Q1529)))

 

[MartyS]

Hi,

I used your exact formula from within VBA and could not replicate the problem you've mentioned.

Here's the full TEST code I used, and it immediately & successfully applied the conditional formatting.

Sub test()




    Range("R29:R35").Select
    
        Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=AND($R29=" & Chr(34) & "Phase 2" & Chr(34) & ",$Q29>AVERAGEIF($R29:$R1529," & Chr(34) & "Phase 2" & Chr(34) & ",$Q29:$Q1529)+STDEV.P(IF($R29:$R1529=" & Chr(34) & "Phase 2" & Chr(34) & ",$Q29:$Q1529)))"


        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Font
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = 0
        End With
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 5287936
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = False




'=AND($R29="Phase 2",$Q29>AVERAGEIF($R29:$R1529,"Phase 2",$Q29:$Q1529)+STDEV.P(IF($R29:$R1529="Phase 2",$Q29:$Q1529)))


End Sub

I would hazard a guess this might have something to do with screenupdating.

You could try, immediately after having set the formatting (e.g. in the above code, it would be just below Selection.FormatConditions(1).StopIfTrue = false) the following:

Application.ScreenUpdating = True

which forces a refresh.

 

[Orion19]

Thank you so much for the reply!!

I actually replaced my code with yours and the exact same thing happened. No formatting after running the macro but when I check conditional formatting the formula is in place. As soon as I click OK the formatting starts to work.

I set ScreenUpdating to false in the beginning of my macro but set it to true at the very end (I did that to try and improve how long it took to run the macro). I have conditional formatting working without an issue in the column next to the one in question. Can ScreenUpdating still be the issue by chance?

Thank you again for your feedback!

 

[MartyS]

That's interesting.

Two things to try.

1. I'm not sure if you put the test code into a NEW workbook, or into the one you've been having problems with.

If you put it into the existing one.. try closing Excel down completely.. and starting afresh... create a new workbook, new module.. and paste the TEST code into there. Add some test data (type some in that you know will highlight / trigger the condition) and then run the macro.

If it works in this brand new "clean" workbook, it'll have most likely pointed to something specific to your existing workbook



2. Another thing to try is the EnableFormatConditionsCalculation

... if you go to the (code) Module in the VBA editor where you've just put the conditional formatting test code, and make sure the properties window is visible (View: Properties Window) or F4

You should see a property called EnableFormatConditionsCalculation make sure it is set to TRUE.


Do either of those help / progress things?

 

[Orion19]

Ok, I followed your instructions verbatim and it worked perfectly in the new workbook. I guess that means something in my code is screwed up. What's odd is that the column next to the column in question has nearly identical code that runs conditional formatting without an issue. Any idea what I should be looking for?

Thank you so much for the tips. Narrowing the problem down to my code at least limits the options.

That's interesting.

Two things to try.

1. I'm not sure if you put the test code into a NEW workbook, or into the one you've been having problems with.

If you put it into the existing one.. try closing Excel down completely.. and starting afresh... create a new workbook, new module.. and paste the TEST code into there. Add some test data (type some in that you know will highlight / trigger the condition) and then run the macro.

If it works in this brand new "clean" workbook, it'll have most likely pointed to something specific to your existing workbook



2. Another thing to try is the EnableFormatConditionsCalculation

... if you go to the (code) Module in the VBA editor where you've just put the conditional formatting test code, and make sure the properties window is visible (View: Properties Window) or F4

You should see a property called EnableFormatConditionsCalculation make sure it is set to TRUE.


Do either of those help / progress things?

 

[MartyS]

If your data isn't confidential, I'd be happy to look at the workbook if you want to provide a link so that I can download it.
(Or maybe put dummy data in it that you know will activate the conditional formatting.. when the cond.formatting is working properly!!)

It's difficult to diagnose code problems when not able to see what code is run before/after the code in question.

Can I also just quickly check: For the conditional formatting that doesn't work, did you replace the code with what I'd shared?

If NOT: you might quickly check the formula in the section of code that adds the conditional formatting code

i.e.

Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=AND($R29=" & Chr(34) & "Phase 2" & Chr(34) & ",$Q29>AVERAGEIF($R29:$R1529," & Chr(34) & "Phase 2" & Chr(34) & ",$Q29:$Q1529)+STDEV.P(IF($R29:$R1529=" & Chr(34) & "Phase 2" & Chr(34) & ",$Q29:$Q1529)))"

and make sure there isn't a problem with quotation marks, etc

i.e. some people will use multiple quotation marks instead of the chr(34) code, such as: "=AND """"Phase2""", $Q29"


If there's an incorrect number of quotation marks, this might stop the formula from working and it might be that when you look at it in the editor, it auto-corrects..
Though not likely, it's worth quickly checking.

 

[Orion19]

I figured it out! Kinda.

I'm half a bottle of wine in trying to figure this thing out. I tried replacing the double quotations with the chr(34) code but didn't have any luck. I also replaced all my conditional formatting code with yours but it produced the same problem. However, just now I removed part of the formula. The standard deviation portion of the formula should have been ran as an array while the AVERAGEIF function should be ran normally. I think that may have been the issue but I'm not sure. All I did was calculate the standard deviation I needed in a separate cell (as an array). Then I had the conditional formatting formula reference that cell instead of trying to run the array. It worked!!!

Thank you SO MUCH for all your help. I am grateful. Do you think that the array is what confused Excel or was it something else?

 

[MartyS]

Seeing things with a new pair of eyes = half a bottle of wine!!

GREAT! I'm so pleased it's now working how you'd like it to.




Would it be possible for you to show the code for the two versions?

i.e. for the one where you split the formula and put the ST.Dev into a cell, then used a different formula for the Cond.Formatting...

could you include the vba code for the cond.formatting AND the cell reference you put the St.Dev formula into plus the St.Dev formula:


e.g.

In cell A1 ...

=StDev.p(B1, B7)



in vba ...


range("D3").select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=AND($R29=" & Chr(34) & "Phase 2" & Chr(34) & ",$Q29>AVERAGEIF($R29:$R1529," & Chr(34) & "Phase 2" & Chr(34) & ",$Q29:$Q1529) + A1




versus the one where you put it in it's entirety into the Cond.Formatting formula.


e.g.

in vba

range("D3").select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=AND($R29=" & Chr(34) & "Phase 2" & Chr(34) & ",$Q29>AVERAGEIF($R29:$R1529," & Chr(34) & "Phase 2" & Chr(34) & ",$Q29:$Q1529)+STDEV.P(IF($R29:$R1529=" & Chr(34) & "Phase 2" & Chr(34) & ",$Q29:$Q1529)))"

 

[Orion19]

Sure!

Here was my original code:In VBA:Range("$Q29:$Q1529").Select
With Range("Q29:Q1529")
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND($R29=""Phase 1 RR"",$Q29>AVERAGEIF($R29:$R1529,""Phase 1 RR"",$Q29:$Q1529)+STDEV.P(IF($R29:$R1529="Phase 2",$Q29:$Q1529)))"
With .FormatConditions(.FormatConditions.Count)
.SetFirstPriority
With .Interior
.PatternColorIndex = xlAutomatic
.ColorIndex = 1
.TintAndShade = 0
End With
End With
With Selection.FormatConditions(1).Font
.ColorIndex = 3
.TintAndShade = 0
End With
End With


I got rid of the standard deviation portion of the equation and entered this in a cell in Excel (not VBA):
In cell $D$27:

{=STDEV.P(IF($R29:$R1529="Phase 2",$Q29:$Q1529))}

Then I replaced the code with the following. $D$27 is the reference to the cell that contains the standard deviation array.
With Range("Q29:Q1529")
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND($R29=""Phase 2"",$Q29>AVERAGEIF($R29:$R1529,""Phase 2"",$Q29:$Q1529)+$D$27)"
With .FormatConditions(.FormatConditions.Count)
.SetFirstPriority
With .Interior
.PatternColorIndex = xlAutomatic
.ColorIndex = 1
.TintAndShade = 0
End With
End With
Range("Q29:Q1529").Select
With Selection.FormatConditions(1).Font
.ColorIndex = 3
.TintAndShade = 0
End With
End With



The more I think about it the more I think it's the array that messed it up. Had the whole formula ran as an array it may have worked. But there's really no way for Excel to know which part of the formula runs as an array and which does not (as far as I can tell at least). Thank you again for you help!