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

worksheet functions.

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

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 example, 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-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)

End Function