Wednesday, June 15, 2016

Conditional Formatting of Cells Containing Formula (Excel pre-2016)

Excel 2016 has a built in function IsFormula to return if the cell contains formula reference or values. As this function is not available in Excel 2010 or earlier editions, one workaround is to create a custom or user defined function in VBA.

In case you do not want to enable Macro or want to avoid VBA, the alternative is to use XLM (Excel 4 Macro) which was the original Excel Macro language before VBA was introduced in Excel 5.0.

In the Name Manager under Formulas ribbon, create a new Name - let's call it IsFormula which should refer to =GET.CELL(48,INDIRECT("rc",FALSE))

Create a new conditional formatting rule with the formula "=IsFormula" with a custom format and apply that on the range where you want to check for cells containing formula.