Microsoft Office Tutorials and References
In Depth Information
Chapter 19: Conditional Formatting and Data Validation
Part VI provides an overview of VBA, with specific information about creating custom
The companion CD-ROM contains all the examples in this section. The file is named
conditional formatting with VBA functions.xlsm .
Identifying formula cells
Oddly, Excel does not have a function that determines whether a cell contains a formula. When
Excel lacks a feature, you often can overcome the limitation by using VBA. 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
Function CELLHASFORMULA(cell) As Boolean
CELLHASFORMULA = cell.HasFormula
After you enter this function into a VBA module, you can use the function in your worksheet
formulas. For example, the following formula returns TRUE if cell A1 contains a formula:
And you also can use this function in a conditional formatting formula. The worksheet in Figure
19-23, for example, uses conditional formatting to identify cells that contain a formula. In this
case, formula cells are displayed in bold, with a background color.
Another way to identify formula cells is to choose Home➜Editing➜Find & Select➜Go
To Special, which displays the Go To Special dialog box. Choose the Formulas option
and click OK to select all cells that contain a formula.
Identifying date cells
Excel also lacks a function to determine whether a cell contains a date. The following VBA
function, which uses the VBA IsDate function, overcomes this limitation. The custom CELLHASDATE
function returns TRUE if the cell contains a date.
Function CELLHASDATE(cell) As Boolean
CELLHASDATE = IsDate(cell)