Microsoft Office Tutorials and References

In Depth Information

This section provides three examples of VBA functions that you can use in conditional formatting formulas.

Part VI provides an overview of VBA, with specific information about creating custom

worksheet functions.

This topic's website contains all the examples in this section. The file is named condi-

tional formatting with VBA functions.xlsm.

Identifying formula cells

Excel 2013 introduced a new worksheet function, ISFORMULA, which returns TRUE if the referenced cell

contains a formula. You can use this function in a conditional formatting formula to highlight all of the formula

cells in a range.

If your workbook must be compatible with earlier versions of Excel, you create a simple VBA function. The

following custom VBA function uses the VBA HasFormula property. The function, which you can enter into a

VBA module, returns TRUE if the cell (specified as its argument) contains a formula; otherwise, it returns

FALSE.

Function CELLHASFORMULA(cell) As Boolean

CELLHASFORMULA = cell.HasFormula

End Function

After you enter this function into a VBA module, you can use the function in your worksheet formulas. For ex-

ample, the following formula returns TRUE if cell A1 contains a formula:

=CELLHASFORMULA(A1)

And you also can use this function in a conditional formatting formula. The worksheet in Figure 19-20, for ex-

ample, uses conditional formatting to identify cells that contain a formula. In this case, formula cells display a

background color.