Returning cell formatting information

This section contains a number of custom functions that return information about a cell's formatting. These functions are useful if you need to sort data based on formatting (for example, sort such that all bold cells are together). Keep in mind that these functions work only with explicitly-applied formatting; they do not work for formatting applied using conditional formatting. Caution

You'll find that these functions aren't always updated automatically. This is because changing formatting, for example, doesn't trigger Excel's recalculation engine. To force a global recalculation (and update all the custom functions), press Ctrl+Alt+F9.

Alternatively, you can add the following statement to your function:

Application.Volatile

When this statement is present, then pressing F9 will recalculate the function.

The following function returns true if its single-cell argument has bold formatting. If a range is passed as the argument, the function uses the upper-left cell of the range.

Function IsBold(cell) As Boolean ' Returns TRUE if cell is bold

IsBold = cell.Range( "A1" ) .Font.Bold End Function

The following function returns true if its single-cell argument has italic formatting:

Function IsItalic(cell) As Boolean ' Returns TRUE if cell is italic

IsItalic = cell.Range ("A1") .Font.Italic End Function

Both of the preceding functions will return an error if the cell has mixed formatting - for example, if only some characters are bold. The following function returns true only if all characters in the cell are bold:

Function AllBold(cell) As Boolean

' Returns TRUE if all characters in cell are bold

If IsNull(cell.Font.Bold) Then AllBold = False

Else

AllBold = cell.Font.Bold End If End Function

The ALLBOLD function can be simplified as follows:

Function AllBold (cell) As Boolean

' Returns TRUE if all characters in cell are bold

AllBold = Not IsNull(cell.Font.Bold) End Function

The FillColor function returns an integer that corresponds to the color index of the cell's interior. The actual color depends on the workbook theme that's applied. If the cell's interior is not filled, the function returns -4142 .

Function FillColor(cell) As Integer ' Returns an integer corresponding to ' cell's interior color

FillColor = cell.Range("A1").Interior.ColorIndex End Function

0 0

Post a comment