Count Bold Cells in a Range

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 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
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 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 Deepak Nayak
Time: Monday, April 13, 2009, 11:20 am

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

Comment from Adam
Time: Friday, October 9, 2009, 5:55 pm

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

Comment from Adam
Time: Friday, October 9, 2009, 7:21 pm

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

Comment from anon
Time: Friday, January 22, 2010, 10:40 am

what about a query that runs every second to count the formatted stuff.
Would that work?

Comment from Max
Time: Friday, March 16, 2012, 8:55 am

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.