MS Excel Question

Factual Questions
1

Is there a way to programmatically detect if the text in a cell is in bold font? For instance, I have a column of numbers. In this column of numbers, some of the entries are bolded. Can I have Excel count up the number of bolded entries? Thanks in advance.

2

You said “programmatically”.

If you really mean that, ie a macro, then yes, it’s trivial to loop through the cells & see if the formatting applied is bold or not. You want something roughly like this:


var boldCount=0
for each cell in range
   if cell.Font.Bold then boldCount = boldCount + 1
next
return boldCount

There is a gotcha that bold can be applied to a cell or to part of the text in a cell. The code above will only detect whole-cell formatting.

If on the other hand, you meant “formulaically”, ie write a formula in one cell which gives the number of bolded cells in some range reference, well then you’re screwed AFAIK.

3

That is true. I do some MS Office development and I always tell people that yes, we can do damned near anything in Excel because it has a native programming language. It is just a matter of time and complexity.

Something like LSLGuy suggests should work fine and isn’t too complicated. Still, I can see how it may be a step above what most power-users have done before.

4

Thanks. It’s been years since I’ve played with macros in Excel. I’ll see if I can make it work.

5

I ended up creating a UDF. Here it is for posterity’s sake:


Function CountBold(rg As Range) As Integer
Application.Volatile
Dim c As Range

For Each c In rg
If c.Font.Bold = True Then
CountBold = CountBold + 1
End If
Next c

End Function
6

The best place I have ever seen to ask Excel questions is the forums on Ozgrid.

Making this function Volatile may not do what you expect. Changing the formatting of a cell does not force recalculation, only changing the value does that. So if you use this function it will not automatically recalculate the number of bold cells if all that changes is the bolding. (Neither will the Worksheet_Change event be triggered for a format change.)

Also, you do not have to test a logical expression to see if it’s equal to True. This is an admittedly trivial point and completely a matter of personal taste. You can just do this:


Function CountBold(rg As Range) As Integer

   Application.Volatile
   Dim c As Range

   For Each c In rg
      If c.Font.Bold Then
         CountBold = CountBold + 1
      End If
   Next c

End Function
7

Thanks. Yeah, I admit it. I copied and pasted this after a little more internet searching. It originally summed the bold cells instead of just counting them so I just stopped tweaking it as soon as it gave the right answer.