EnableFormatConditionsCalculation

[PetLahev]

Hello
I've just found this property in Excel 2007 object model and have tested it.

But it seems doesn't works. Does anybody experience with that?

I wrote this code for testing

Sub TestEnableFormatConditionsCalculation()

' Does it work?

    Dim wks As Worksheet
    
    Set wks = ActiveSheet
    
    ' a) directly access to a sheet
    'Sheet1.EnableFormatConditionsCalculation = False
    ' b) Sheets method
    'Sheets("Sheet1").EnableFormatConditionsCalculation = False
    ' c) Wprksheets method
    'Worksheets("Sheet1").EnableFormatConditionsCalculation = False
    ' d) Activeshee method
    'ActiveSheet.EnableFormatConditionsCalculation = False
    ' e) object variable
    wks.EnableFormatConditionsCalculation = False
     
    With ActiveCell
        .FormatConditions.Add Type:=xlCellValue, _
                          Operator:=xlGreater, _
                          Formula1:="=10"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 13551615
            .TintAndShade = 0
        End With
    
    End With
    
    ' a) directly access to a sheet
    'Sheet1.EnableFormatConditionsCalculation = True
    ' b) Sheets method
    'Sheets("Sheet1").EnableFormatConditionsCalculation = True
    ' c) Wprksheets method
    'Worksheets("Sheet1").EnableFormatConditionsCalculation = True
    ' d) Activeshee method
    'ActiveSheet.EnableFormatConditionsCalculation = True
    ' e) object variable
    wks.EnableFormatConditionsCalculation = True
   
End Sub

 

[Andrew Poulsom]

I can't get it to work as I described in Excel 2007.

 

[MSC_GG]

Ok, maybe different implementations. I'll try on 2010 when I have the opportunity. Thank you

 

[Gladiator51]

Hello..did you ever get resolution on the EnableFormatConditionsCalculation behavior ? I have a VBA application which uses conditional formatting extensively and sets flag to FALSE, yet the format rules seem to continue to be evaluated. Thanks !

 

[MSC_GG]

Hi Gladiator51,

No resolution so far. I've had the chance to try with Excel 2010, following Andrew's example to the letter, and I still get the same behavior: after scrolling or switching tabs after step 4, you can see the conditional format update (i.e. B1 cell background color is no longer red).

I don't know if Andrew tested exactly that. To me, this feature is bugged.

 

[gneil]

This looks like it could be very useful for me.
I am running many scenarios via vba code (screenupdating is false) I am not interested in the conditional formatting until the code has ran, can I use this to turn off conditional formatting during the scenario runs to reduce the calculation time? I notice it does not work simpoly a s a suppression of the formatting during normal calculations but wondered if with screenupdating off it might.
We have generally had to eliminate Cond Formats as a way of streamlining the calculations during the batch runs which can take several tens of minutes.
Any thoughts?

Neil

It works for me in Excel 2010.

This is what I did to test:

1. In A1 I entered 6
2. In B1 I applied the Conditional Formatting formula =$A$1>5 and set the fill to red.
3. With that format condition evaluating to True I ran the following procedure:

Sub FCOff()    ActiveSheet.EnableFormatConditionsCalculation = False
End Sub

4. I changed the value in A1 from 6 to 3 and there was no change in B1's fill.
5. I ran this procedure:

Sub FCOn()
    ActiveSheet.EnableFormatConditionsCalculation = True
End Sub

and B1's red fill was removed as expected.