This is the second in a series of Posts on Conditional Formats (see part 1).
This post looks at the effects (and the resulting bugs!) on Conditional Formats of:
- Application.Screenupdating
- Application.EnableConditionalFormatsCalculation
- Application.Calculation
- Whether the cells containing the conditional formats are visible or not
- Screen Refresh
- Excel 2007, Excel 2010 and Excel 2013
I am using the same (but slightly updated) test workbook as in Part 1: you can download it from SkyDrive.
Running the Tests
The FormatConditionsA.xlsb workbook contains 12 VBA subroutines to do the testing (Test1 through Test3C).
Cell B2 uses 2 UDFs (Signal1 and Signal2) to determine whether B2 is even or odd, and the B2 formula refers to D21
Cell E2 has 2 conditional format formulas that test directly whether D21 is even or odd.
You need to run the tests with the VBE window open and the immediate window visible.
For a more detailed explanation of this example workbook see part 1.
If you run the tests using Excel 2007, Excel 2010 and Excel 2013 you will see that a lot of work has been done by the Excel team to minimise the number of times the conditional formats get executed. But (as always when doing optimisations) this has tended to introduce bugs.
Test1: Screenupdating=True, Enable=True, Calc=Auto
- Excel 2013: OK, 1 call to each UDF, large pause of a second or two before cell B2 refreshes its colour.
- Excel 2010: OK , 2 calls to each UDF, no noticeable pause.
- Excel 2007: OK, 4 calls to each UDF, no noticeable pause.
Test2: Screenupdating=true, Enable=true, Calc=manual
- Excel 2007 & Excel 2010: OK
- Excel 2013: Bug in cell B2 – neither of the conditional formats is applied to B2 and neither of the UDFs are executed. Scrolling down and up to refresh the screen does not fix this, but pressing F9 does.
Test3: ScreenUpdating=False, Enable=True, Calc=Manual
- Excel 2013: OK
- Excel 2010: Bug in B2. Neither of conditional formats are applied to B2 and the UDFs are not executed. Page Down Page Up does not fix but F9 does.
- Excel 2007: Bug in B2. Page Down Page Up fixes.
Test1A: Screen=True, EnableFormatConditionsCalculation=False, Calc=Auto
So what does setting EnableFormatConditionsCalculation to False actually do?
I am not sure, but what it does NOT do is to permanently switch off the evalution of conditional formats!
- Excel 2007: OK – the pause in Test 1 has disappeared!
- Excel 2010: Bug in Cell E2. the left-most vertical border is coloured correctly but the rest of the cell is not! Page Down Page Up fixes it.
- Excel 2013: Bug in cell B2 and E2. Page Down Page up fixes it.
Test2A: Screen=True, EnableFormatConditionsCalculation=False, Calc=Manual
- Excel 2007: OK
- Excel 2010: Bug in cell E2. Page Down Page Up fixes it.
- Excel 2013: Bug in cell B2. Neither Page Down Page Up nor F9 fix it, but Ctrl/Alt/F9 does.
Test3A: Screen=False, EnableFormatConditionsCalculation=False, Calc=Manual
- Excel 2013: OK
- Excel 2010: Bug in cell B2: Neither Page Down Page Up nor F9 fix it, but Ctrl/Alt/F9 does
- Excel 2007: Bug in cell B2. Page Down Page Up fixes it.
Tests 1B to 3C: switching to another sheet, run the tests, switch back
Its magic: all these tests run correctly in all versions!
Conclusions
- Looks like using UDFs in conditional format formulas is rather buggy: avoid.
- EnableFormatConditionsCalculation does not look useful.
But there were many reports of a problem importing Excel 2003 files with conditional formats into later versions that could be fixed by setting it to True: I don’t know if this problem still exists. - The safest way is to activate a sheet that does not contain any conditional formats.
