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 " & i & " 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 " & i & " bold cells in " & sAddress & "." End Sub
You can even call it from a worksheet cell, using this syntax.
=CountBoldCellsInRange(A1:F20)
Stéphane Nolf says
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 ?
Jon Peltier says
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
Ajay says
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,
Jon Peltier says
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.
Jon says
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
Jon Peltier says
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.
Anna M. says
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
Jon Peltier says
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.)
Deepak Nayak says
Hi Jon,
Tried the ‘Application.Volatile’ part, but the values in the worksheet have not become dynamic.
-=-=-=-=-=-=-=-=-=-=-
Function CountBoldCellsInRange(rng As Range) As Long
Application.Volatile
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
-=-=-=-=-=-=-=-=-=-=-
What am I doing wrong here?
Regards,
Deepak
Jon Peltier says
Application.Volatile forces a calculation of a UDF whenever the sheet recalculates. It’s not necessary in this formula, since any change or calculation in the cells referenced by the formula will cause recalculation of the UDF anyway. However, changing a format does not invoke a recalculation, so the calculated value will not change if all you are doing is clicking buttons or using a dialog to change formatting. You need to change a value or recalculate the sheet to update the formula result.
Adam says
Jon,
I’m having the same problem that Anna had above, namely that I get a response of “#NAME?”
I have followed your link about how to use someone else’s macro, but that doesn’t really apply, as this is a function.
Here’s what I’m doing:
I created the Function, and alt-Q to save it. I then went into a cell, and started to type “=countbold”, and the whole function name appeared for me to select. I selected it, and put in a range, and got the “#NAME?” error.
Any help would be greatly appreciated.
I’m using Excel 2007, if that’s relevant.
Thank you,
ADam
Adam says
OK, got this. Evidently Excel will not run User Defined Functions, unless you enable macros, and play with the security a little. I’m over that, and have this working.
However, the function counts cells correctly if they are bold.
But, if I have Conditional Formatting set, that makes a particular cell BOLD because it fits some criteria, that cell will NOT be counted by this function.
Any thoughts?
Thanks,
Adam
Jon Peltier says
Adam –
The formatting recognized by VBA is the formatting applied to the cell in the absence of any conditional formatting being turned on. It is possible to write your function so that it also checks the condition, and returns the formatting for that condition. You can either hard code this, or write code to examine the conditions for each cell, and determine which apply, and then extract the formatting, then count the cells.
Did I make it sound complicated? It’s even more complicated than that. I wish I could tell you otherwise.
anon says
what about a query that runs every second to count the formatted stuff.
Would that work?
Jon Peltier says
Anon – I would be reluctant to tie up the processor like that.
Max says
Thank you for this, this is a very helpful tool. However, I receive an error message when I try to do this. In the VBA window, it highlights the first ‘dim’ in the function and says,
“Compile error:
Expected: end of statement”
Can you please tell me what this means and how to fix it? Thanks in advance.
Also, I should add that I have absolutely no idea what I’m doing.
Jon Peltier says
Max –
Make sure you didn’t copy any non-printing characters. Try deleting the line and retyping it.