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.
Search JabSto ::




Custom Search