Microsoft Office Tutorials and References
In Depth Information
UDFs with Conditional Formatting
udfs with conditional formatting
One of the less-utilized but powerful applications of a UDF is to combine it with Conditional
Formatting. Let’s say you want to identify cells that contain a comment in a workbook where the
option to show comment indicators is turned off. It’s true that cells containing comments fall into
the category of SpecialCells and you can select them through the Go To Special dialog box, and
maybe format the selected comment-containing cells from there. However, you’d need to repeat
those steps any time a cell obtains or deletes a comment, and there’s no telling if or when that might
happen.
A better way to go is with a UDF as the formula rule with Conditional Formatting, to format the
comment-containing cells in real time, as comments are added or deleted. For example, place this
UDF into a standard module:
Public Function TestComment(rng As Range) As Boolean
TestComment = Not rng.Comment Is Nothing
End Function
Back onto your worksheet, select the range of interest, in this example starting from cell A1. In the
Conditional Formatting dialog (or the New Formatting Rule dialog for Excel versions starting with
2007), enter this formula:
=TestComment(A1)
Then choose your formatting style, click OK, and all comment-containing cells in that range will be
formatted.
calling your function from a Macro
As I mentioned earlier, functions that you create need not only serve as worksheet formulas. A
function can also be called by a macro, which does not limit the macro’s ability to do whatever needs to
be done. In the following code, the OpenTest function is set apart from the OpenOrClosed macro,
which gives you the best of both worlds for testing if a particular workbook is open or closed.
To test by formula if a workbook named “YourWorkbookName.xls” is open or closed, you can
enter this in a worksheet cell, which will return TRUE (the workbook is open) or FALSE (the
workbook is closed):
=OpenTest(“YourWorkbookName.xls”)
To test by macro, you can expand the functionality by asking with a Yes/No message box if you’d
like to open that workbook if it is not already open, and open it if Yes is selected, or keep the
workbook closed if No is selected. Here’s the code:
Function OpenTest(wb) As Boolean
‘Declare a Workbook variable
Dim wkb As Workbook
Search JabSto ::




Custom Search