Count Bold Cells in a Range

by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2009.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.

I was asked if there is any way to count all of the bold cells in a range. Of course there is. Here is a simple VBA function that carries out this task:

Function CountBoldCellsInRange(rng As Range) As Long
  Dim rCell As Range
  Dim iBold As Long

  iBold = 0
  For Each rCell In rng
    If rCell.Font.Bold Then
      iBold = iBold + 1
    End If
  Next

  CountBoldCellsInRange = iBold
End Function

This function can be called in a number of ways. Here are two examples showing how to call the function from other VBA procedures.

Sub Test1CountBoldCellsInRange()
  Dim i As Long
  If TypeName(Selection) = "Range" Then
    i = CountBoldCellsInRange(Selection)
    MsgBox "There are " &amp;amp;amp; i &amp;amp;amp; " bold cells in the selected range."
  Else
    MsgBox "The selected object is not a range."
  End If
End Sub

Sub Test2CountBoldCellsInRange()
  Dim i As Long
  Dim r As Range
  Dim sAddress As String
  sAddress = "A1:F20"
  Set r = ActiveSheet.Range(sAddress)
  i = CountBoldCellsInRange(r)
  MsgBox "There are " &amp;amp;amp; i &amp;amp;amp; " bold cells in " &amp;amp;amp; sAddress &amp;amp;amp; "."
End Sub

You can even call it from a worksheet cell, using this syntax.

=CountBoldCellsInRange(A1:F20)

Possibly Related Posts:

Bookmark and share this entry:

Posted: Thursday, October 16th, 2008 under VBA.
Comments: 8

Comments

I welcome comments from my readers. If you have an opinion on this post, if you have a question or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion.

Read the PTS Blog Comment Policy.

Comment from Stéphane Nolf
Time: Thursday, October 16, 2008, 5:23 pm

Hi Jon,

Nice little function.

However, I think it is worth mentionning, if I make no mistake, that the function won’t update by itself if used in a worksheet.

I think it is the case for all functions that return a value based on layout.

Maybe you know a workaround ?

Comment from Jon Peltier
Time: Thursday, October 16, 2008, 5:27 pm

Stéphane -

That’s true. You could force the function to recalculate whenever the worksheet calculates by inserting one line into the function:

Application.Volatile

Comment from Ajay
Time: Thursday, October 23, 2008, 9:52 am

Jon - That’s very useful. Another useful function could be to list/count the number of cells marked in a particular color.

I can’t tell you how many times people have asked me if they could somehow seperate out cells which are colored Red (or in some other color) in a spreadsheet :-) Oftentimes people ‘think’ that marking the cells in Red is a good way of showing somebody else what specfic points to pick up for further analysis and they end up complicating things (as opposed to simply using a blank column to mark those points with an Y/N value).

I am not sure if this can be done, but could you write an array formula to seperate out colored cells from a given range and marked in a specific color?

Regards,

Comment from Jon Peltier
Time: Thursday, October 23, 2008, 3:05 pm

Ajay -

This can certainly be done. In fact, I believe it is a feature of ASAP Utilities, an obsessively comprehensive set of tools for working in Excel.

Comment from Jon
Time: Monday, November 3, 2008, 2:43 pm

Hi Jon

I notice this question asked frequently - and usually with a similar UDF solution. I frequently need to count / sum based on some formatting applied, but I tend to refer to excel 4.0 macro’s. More specifically I tend to use the GET.CELL function within a workbook level named range.

E.G: CellColor = GET.CELL(63,A1)

…And then use a SUMIF / COUNTIF

I wonder - why do you think VBA tends to be the most frequented solution? My impression is that excel 4.0 macro’s are not well liked in the excel guru community. What are your feelings toward the excel4.0 macro functions?

Regards
Jon

Comment from Jon Peltier
Time: Monday, November 3, 2008, 2:58 pm

Microsoft has been trying since Excel 5/95 to deprecate XLM. They can’t for two reasons. 1 - a lot of business solutions were coded in XLM and companies would refuse to upgrade rather than rewrite these in VBA. 2 - a certain number of things can be done using XLM which are not possible in VBA. Until Microsoft addresses these two items, they will have to support XLM.

However, the Microsoft efforts to de-emphasize XLM have been successful, in that few who picked up Excel at or after Excel 5/95 have been exposed to XLM. Those of us who learned XLM before VBA have forgotten most of the XLM we ever knew.

If the Get.Cell XLM functions could be incorporated into Excel’s library of worksheet functions, it would go a long way toward their goal of making XLM unnecessary.

Comment from Anna M.
Time: Wednesday, November 12, 2008, 9:41 am

I added the line write into the worksheet cell and i am not doing something correct. I am getting an error

=CountBoldCellsInRange(AJ5:AJ7)

#NAME?

Not sure why this is not working

Comment from Jon Peltier
Time: Wednesday, November 12, 2008, 10:56 am

Anna -

#NAME? means that Excel doesn’t recognize CountBoldCellsInRange. Did you place the function into a regular code module? Follow the protocol in How To: Use Someone Else’s Macro to make sure you are implementing the function properly. (I usually remember to include a link to that tutorial, but I forgot. My bad.)

Main menu:

Subscribe

Site search

Recent Posts

Recently Commented

Popular Posts

Archive

Categories

Buy me a coffee