Microsoft Office Tutorials and References
In Depth Information
Some Useful Worksheet Functions
Note that this function works only with explicitly applied formatting. It doesn’t work for
formatting applied using conditional formatting. Excel 2010 introduced a new object, DisplayFormat .
This object takes conditional formatting into account. Here’s the IsBold function rewritten so
that it also works with bold formatting applied as a result of conditional formatting:
Function IsBold(cell) As Boolean
‘ Returns TRUE if cell is bold, even if from conditional formatting
IsBold = cell.Range(“A1”).DisplayFormat.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
AllBold = cell.Font.Bold
End If
End Function
You can simplify the AllBold function 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 isn’t
filled, the function returns –4142 .
This function doesn’t work with fill colors applied in tables (created with Insert➜Tables➜Table)
or pivot tables. You need to use the DisplayFormat object to detect that type of fill color, as I
described previously.
Search JabSto ::

Custom Search