# MS Excel Question

**URL:** <https://boards.straightdope.com/t/ms-excel-question/407042>\
**Category:** Factual Questions\
**Created:** [June 6, 2007, 9:19pm UTC](https://boards.straightdope.com/t/ms-excel-question/407042 "2007-06-06T21:19:31Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![UncleRojelio](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/unclerojelio/32/3160_2.png) [@UncleRojelio](https://boards.straightdope.com/u/UncleRojelio)\
**Post date:** [June 6, 2007, 9:19pm UTC](https://boards.straightdope.com/t/ms-excel-question/407042/1 "2007-06-06T21:19:31Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![LSLGuy](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lslguy/32/5813_2.png) [@LSLGuy](https://boards.straightdope.com/u/LSLGuy)\
**Post date:** [June 7, 2007, 2:01am UTC](https://boards.straightdope.com/t/ms-excel-question/407042/2 "2007-06-07T02:01:52Z")

</div>

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:

```auto

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.

---

<div class="post-metadata">

**Author:** ![Shagnasty](https://avatars.discourse-cdn.com/v4/letter/s/9dc877/32.png) [@Shagnasty](https://boards.straightdope.com/u/Shagnasty)\
**Post date:** [June 7, 2007, 2:10am UTC](https://boards.straightdope.com/t/ms-excel-question/407042/3 "2007-06-07T02:10:31Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![UncleRojelio](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/unclerojelio/32/3160_2.png) [@UncleRojelio](https://boards.straightdope.com/u/UncleRojelio)\
**Post date:** [June 7, 2007, 3:08am UTC](https://boards.straightdope.com/t/ms-excel-question/407042/4 "2007-06-07T03:08:56Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![UncleRojelio](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/unclerojelio/32/3160_2.png) [@UncleRojelio](https://boards.straightdope.com/u/UncleRojelio)\
**Post date:** [June 7, 2007, 12:10pm UTC](https://boards.straightdope.com/t/ms-excel-question/407042/5 "2007-06-07T12:10:08Z")

</div>

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

```auto

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

```

---

<div class="post-metadata">

**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\
**Post date:** [June 7, 2007, 1:28pm UTC](https://boards.straightdope.com/t/ms-excel-question/407042/6 "2007-06-07T13:28:09Z")

</div>

The best place I have ever seen to ask Excel questions is the [forums on Ozgrid](http://www.ozgrid.com/forum/forumdisplay.php?f=8).

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:

```auto

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

```

---

<div class="post-metadata">

**Author:** ![UncleRojelio](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/unclerojelio/32/3160_2.png) [@UncleRojelio](https://boards.straightdope.com/u/UncleRojelio)\
**Post date:** [June 7, 2007, 1:42pm UTC](https://boards.straightdope.com/t/ms-excel-question/407042/7 "2007-06-07T13:42:59Z")

</div>

[QUOTE=CookingWithGas]

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.  
[/QUOTE]  
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.