Count Bold - need to Recalculate?

[faureman via OfficeKB.com]

All,

I have used some code (found on this site - thank you!) that will effectively
count the number of cells in a range that are bold. Here is the code:

Function CountBold(rg As Range) As Long
'originally posted by Ron Rosenfeld
Dim c As Range
For Each c In rg
CountBold = CountBold - c.Font.Bold
Next c
End Function

The cell that is "counting" uses this formula: =countBold(K11:L15)

The formula works! However, the issue is that it will not automatically
recalculate if I "bold" or "unbold" one of the cells in the range - even
though "Recalculate" (F9) is set to Automatic.

The only way the formula updates is to click into the formula cell and hit
enter.

Please explain how I can get this to update automatically upon bolding or
unbolding a cell found in the range.

Thanks.

 

[T. Valko]

Please explain how I can get this to update automatically upon

bolding or unbolding a cell found in the range

You can't!

As you've discovered, changing a cells format does not trigger a
calculation. So, if you change a format the formula does not update.

You can add this line of code:

Application.Volatile

Before the line: Dim c As Range

However, the formula still will not update when you change a format *but*
the formula will update once some other event triggers a calculation or you
manually force a calculation.

It's because of this behavior that I discourage the use of calculations
based on cell formats.