Conditional Format recalc oddity

This came up on the Excel-L list recently – maybe some reader can explain it.

The requirement was to base a Conditional Format on a user-defined expression entered as text.

The easy answer is for them to enter it as a formula and then refer to that cell in the CF so that you can see both the formula in the cell and the result, which should be consistent with the CF.  But the requirement was to have the expression as literal text – for example B6 can have the entry “=OR(F$12=Choice.Age,F$12=Choice.ISA,F$12=Choice.LIA, F$12=100) ”

One solution is to define an Eval() UDF in a standard module which evaluates the expression and then use =Eval(B6)

Function Eval(strExpr As String) As Variant 
Eval = Application.Evaluate(strExpr) 
End Function

To use a UDF requires enabling macros. You cannot use a UDF in another workbook or from an addin – attempting that gives the error message “This type of reference cannot be used in a Conditional formatting formula” which is remarkably underdocumented.
To avoid the need for an Eval() UDF, I considered the use of the XLM =EVALUATE() function. XLM (Excel 4) macros can only be accessed in Excel from defined names.

Define a name Conditional.Format.Key.Ages=EVALUATE(Sheet1!$B$6)
and enter =Conditional.Format.Key.Ages in a cell and you get the formula result. But EVALUATE  is non-volatile so it would not recalculate when any input cells change. To make the name volatile, add a reference to the current time and make it T(NOW()) to it to convert the time to a null string.

Define a name Conditional.Format.Key.Ages=EVALUATE(Sheet1!$B$6&T(NOW()))
and that now recalculates with the sheet.

If you set a CF based on the CELL with the formula =Conditional.Format.Key.Ages
that works fine and responds to changes.

BUT if you set the CF directly as =Conditional.Format.Key.Ages
it does not recalculate on a change of cell F12 and only does so with function key F2+Enter, or Ctrl+Alt+F9

I wonder why?

This is curious in view of the fact that CF formulas are “super volatile” according to Charles Williams, the guru on excel performance. That is why, for example worksheets with thousands of formatconditions, caused by excessive copy & paste by users from one sheet to another, render so slowly on the screen – the calculation is done every time the cell is rendered. The users think the problem is automatic calculation and turn it to manual, which adds to their risks.


About Patrick O'Beirne, Excel/VBA developer

Patrick provides consultancy and training in spreadsheet automation, data analysis, testing and model review; and the Excel spreadsheet auditing addin XLtest
This entry was posted in Excel/VBA and tagged , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s