Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count Bold - need to Recalculate?
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. -- Message posted via http://www.officekb.com |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count Bold - need to Recalculate?
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. -- Biff Microsoft Excel MVP "faureman via OfficeKB.com" wrote: 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. -- Message posted via http://www.officekb.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Bold Text | Excel Worksheet Functions | |||
How to count number of Cell have Strike Through Effect & Bold as font style. | Excel Discussion (Misc queries) | |||
Join bold and non-bold text in one cell | Excel Discussion (Misc queries) | |||
How do I ask Excel to count how many items are in BOLD TYPE? | Excel Discussion (Misc queries) | |||
Count bold text values | Excel Worksheet Functions |