Count based on formatting (Excel 2000 SR-1)

Topic

New Reply

Does anyone know if there is quick way to count items in a column, or named range if necessary, that do not have a bold format applied to them. In this list I built, I have used bold to format Categories and then listed the sub-categories under them unbolded. I simply want to count the non-bold entires. I have looked at a number of the different Count functions and it is not obvious whether they can do this, or do I have to have a small macro that uses the IF function. Any insight that anyone can provide is greatly appreciated. Thanks.

Ron M

Reply | Quote

Replies

You’ll have to use a user-defined VBA function for this:

Function CountNotBold(MyRange As Range) As Long
Dim oCell As Range
Dim lngCount As Long
For Each oCell In MyRange
If oCell.Font.Bold = False Then
lngCount = lngCount + 1
End If
Next oCell
CountNotBold = lngCount
Set oCell = Nothing
End Function

To count the number of not-bolded cells in A3:A25, put the formula =CountNotBold(A3:A25) in a cell.

Notes:
You could use a function CountBold that counts the bolded cells instead, and subtract this from CountA(range).
The function CountNotBold includes empty cells in its count. If you want to ignore empty cells, build in an extra check.

Reply | Quote

edited – Hans was faster and more through. -Use his

Ron,
You can create a funtion to test for bold. See the attached workbook. The count the False hits.

Function CellBold(Cell)
Application.Volatile
CellBold = Cell.font.Bold
End Function

Hope this helps,
Chuck

Reply | Quote

Thanks to both Hans and Reimer. It was as I suspected. Hans, thanks for the VBA Function. Can I run it separately, or does it have to be inside a subroutine – still learning VBA . Upon rereading, I see how to make it work. Many thanks…

Ron M

Reply | Quote

You can run Hans’ function by just putting a formula like =CountNotBold(A3:A25) in a cell as he said in his message.

One note about these functions. Changing the bold property of the font for a cell is not considered by Excel to be an event that causes a recalculate event. Therefore, if you have a formula like the one above in a cell, and you change the bold property of a cell or cells in the range A3:A25, the formula will not automatically recalculate. You will need to manually force a recalculate which can be done by selecting the cell containing the formula and then pressing F2 and Enter.

Reply | Quote

Legare, thanks for the caution on the recalculate.

Hans, I have run the function and I get a #NAME? error associated with it. I am wondering if this is a function of how I maintain my macros and functions? I keep them in a spreadsheet personal.xls that I keep in the XLSTART folder. Do I need to have the function in the worksheet that uses it?

Any insight you can provide is greatly appreciated. Thanks.

Ron M

Reply | Quote

If the function is in a module in the active workbook, you can use =CountNotBold(…)
If the function is in a module in another workbook, you must prefix it with the workbook name and !, in this case =Personal.xls!CountNotBold(…)
If the function is in a module in an add-in (.xla), you can use =CountNotBold(…), just as if it was in the active workbook.

Reply | Quote

Thanks Hans. That helps to clarify the situation.

Regards,
Ron

Reply | Quote