User-defined functions to get cell formatting

Sometimes you want to check the colour of a cell, or whether a cell is bold. This can be easily done with user-defined functions (UDFs). (To create a UDF, press Alt-F11, Alt-I-M, and type the code below.)

User defined functions to get the background colour and bold value of a cell

You can use ISBOLD(cell) to check if a cell is bold, and BGCOLOR(cell) to get the background colour of the cell. This lets you selectively process bold or shaded cells. The two examples below show how you can add only the cells in bold, or only the shaded cells.

Example to selectively add shaded cells

Example to selectively add bold cells

Rather than use an additional column for ISBOLD or BGCOLOR, you can use an array formula, like below. (Remember to press Ctrl-Shift-Enter instead of Enter after typing this formula)

Example to selectively add bold cells using a single array formula

But first, you need to change the UDF to return an array rather than a single value. So IsBold will have to be modified as shown.

User defined function isBold modified to return an array

User-defined functions that process arrays can be very powerful. It can bring the full power of functional programming into Excel. I’ll describe some next week.

PS: In case you’re wondering, Application.Volatile tells Excel to recalculate the function every time the worksheet is recalculated. When a cell is made bold, or shaded, the value of the cell doesn’t change. So Excel doesn’t recalculate any formulas. You’ll have to manually press F9 every time to recalculate these cells. And Application.Volatile ensures that when you press F9, these functions are recalculated.

2 thoughts on “User-defined functions to get cell formatting”

  1. good… May i ask your help to give me a solution for my problem… I always use vlookup to find some data in a specific range, my lookup value are employee code number then look for there name, age, address and so on. but today, i want look up a value with multiple returns. i want to have the some of a worker which he have in a specific date. Please give help regarding this matter.

  2. Hi Need a help
    The ISBOLD function which is defined on top does not work if I change the cell which is Bold to Unbold.
    The formula should work automatically if a cell is changed BOLD or UNBOLD

Leave a Comment

Your email address will not be published. Required fields are marked *