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]

In what way doesn't it work?

http://msdn.microsoft.com/en-us/library/bb216237.aspx

 

[PetLahev]

The help says

When set to True (default), evaluation of conditional formats will will occur automatically as needed. When set to False, conditional formats will not be re-evaluated. Any previously applied conditional formatting will still be visible, but it will not update as cell values or AppliesTo ranges are changed.

The purpose of this flag is to allow VBA programmers to configure a rule completely before evaluating it. This is particularly useful when condition is applied over a large range as performance can be slow in these cases.

But when my code is running and EnableFormatConditionsCalculation is being set to False and then I go to a sheet where I have some FormatConditions and change one of them. The FormatConditions still works even thought the help says "no"

 

[Andrew Poulsom]

It means that conditional formatting formulas will not be evaluated when the worksheet is calculated (I think). It says "it will not update as cell values or AppliesTo ranges are changed". It doesn't mention a conditional format being changed.

 

[PetLahev]

Ohhh you are right, thanks for explaining

 

[levani]

hello, I am new here. I have one question about excel and i think that it is able with EnableFormatConditionsCalculation function.

my question is simple, here is example:
A1=1500, B1=500, C1=A1-B1 (so it is 1000 (1500-500))

my question: is there any way to do something with this example to get in C1 1500 still?

I try to change format for B1, for example activate "Text" format for it, but it does not work.

I want both: 1) in C1 to be formula A1-B1 and 2) in C1 get sum 1500.

Is there any way to do it?

Thanks in advance

 

[Andrew Poulsom]

Why 1500? Do you mean?

=MAX(1500,A1-B1)

 

[MSC_GG]

It means that conditional formatting formulas will not be evaluated when the worksheet is calculated (I think). It says "it will not update as cell values or AppliesTo ranges are changed". It doesn't mention a conditional format being changed.

Sorry Andrew to unearth this topic but I don't understand your interpretation. The documentation also reads: "The purpose of this flag is to allow VBA programmers to configure a rule completely before evaluating it. This is particularly useful when condition is applied over a large range as performance can be slow in these cases."

This is exactly my requirement as a programmer at the moment: I apply via a macro a conditional formatting rule to a large range of cells (not very complex though, but it still slows performance down when I'm browsing/editing my worksheet) and I want all the formats (font and background color, basically) to be updated only when the user clicks a Refresh button that triggers the relevant macro. So I've setup the property EnableFormatConditionsCalculation to False to all my worksheets (to be sure). And yet... whenever I change one of the values used as an input of my conditional formatting rule's formula, the target range's formats get updated. Which proves Excel keeps using resources to re-evaluate all the formulas on the fly. Which I precisely forbid - at least that was my intention.

So I may be missing something fundamental here (and I've tried with a very simple workbook with 2 cells as inputs for a dummy format condition formula and 1 cell as target range) and no matter how I set the EnableFormatConditionsCalculation property, I still observe the same behaviour.

Please enlighten me.

 

[Andrew Poulsom]

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.

 

[MSC_GG]

Thanks for your reply. Between step 4 and step 5, did you try to scroll up/down or switch to another worksheet and then come back?
I followed exactly your steps: there seems to be no change after setting A1 to 3 at step 4, but that's an illusion from the GUI. The new format has been applied and is revealed as soon as relevant parts of the GUI are refreshed via scrolling or switching tabs/windows. I'm using Excel 2007 on Windows 7.