count only bold entries

Hi,

Prehaps this code will help.

Press Alt + F11 and paste this code into a module

http://www.contextures.com/xlvba01.html#Regular

Please Login or Register  to view this content.

Then use a forumla like

=CountBold(A1:A10)

To recalc press F9.
VBA Noob

jim,

In your worksheet, press ALT+F11. Then, you should see in the left-hand column something like 'VBA Projects (YourWorkbookName.xls)'. Left-click on that and choose Insert -> Module.

Copy and paste VBANoob's code into that module, then close the VBA window. Save your file.

Now, from any cell in that workbook, you can type the formula:

=COUNTBOLD(whatever_range_you_want)

For example, if you wanted to count the bold-text cells in the range A5 to B10, use the formula

=COUNTBOLD(A5:B10)

Once you press enter, that cell will tell you how many cells are formatted as bold font in the range you specified.

Sidenote to VBANoob - could the 'Application.Volatile' statement be added so that the formula recalcs automatically? Or am I thinking of something else?

Good morning jimb0693

Have a look at the attached spreadsheet.

Paul

Sidenote to VBANoob - could the 'Application.Volatile' statement be added so that the formula recalcs automatically? Or am I thinking of something else?

No Paul, it can't, but you are thinking on the right lines.

Application.Volatile will force a calculation to update automatically but for reasons known only to Microsoft it won't work when you are using a formula that interacts with formats (colours / bold / italic etc). As the Application.Volatile command uses its own little slice of processing power, and it's compeltely irrelevant in this instance theres no real point in using it.

HTH

DominicB

Hi jimb0693

There shouldn't be. The file I uploaded was done using Excel 2003.
You're not using Excel 2003 Viewer are you? If so, that's not capable of running macros.

If that's not the case the only other thing I can think of is that you left some components off when you installed the software. I can't remember whether you can leave out VBA in its entirety when you install Office...?

HTH

DominicB