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 Application.Volatile 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.