Fix an Excel VBA Function to Count Cells by Text and Bold Formatting
Question details
The user needs an Excel VBA function that counts cells matching both the text and bold formatting of a criteria cell.

- Product
- Microsoft Excel VBA
- Device & OS
- not provided
- Scenario
- A custom worksheet function compares each cell in a range with a criteria cell's value and bold formatting.
- Observed behavior
- The function may return a #VALUE! error when the Font.Bold property is handled incorrectly.
Use a single criteria cell containing the text and bold state you want to match. Make sure the criteria cell and cells being counted have consistent whole-cell formatting rather than mixed bold formatting within a cell.
Store the Criteria Cell's Bold State in a Boolean Variable
Use this VBA function to count cells whose values and bold formatting match a single criteria cell.
In desktop Excel, open the workbook and select Developer > Visual Basic. In the VBA editor, select Insert > Module.
Paste this code into the module: Function CountifBoldCells(Rng_b As Range, criteria_b As Range) As Long Dim bcell As Range Dim cellb As Boolean cellb = criteria_b.Font.Bold For Each bcell In Rng_b If bcell.Font.Bold = cellb And _ bcell.Value = criteria_b.Value Then CountifBoldCells = CountifBoldCells + 1 End If Next bcell End Function
Return to the worksheet and enter =CountifBoldCells(A1:A10,D1) in an empty cell. Replace A1:A10 with the range to count and D1 with the single criteria cell.
Select File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm). Enable macros when prompted for a workbook you trust.

Work with Excel Spreadsheets in WPS Office
WPS Office is a free, lightweight alternative for working with spreadsheets in a familiar interface. Its Microsoft Office format support makes it easier to move existing files, although it does not fix Excel VBA errors and macro support can vary by platform and edition.

Frequently Asked Questions
How do I choose which text and bold state to count?
Enter the desired text in a single criteria cell and apply or remove bold formatting. Reference that cell as the second argument, such as D1 in =CountifBoldCells(A1:A10,D1).
Why might the function return #VALUE! for mixed formatting?
If a cell contains both bold and non-bold characters, Font.Bold can return Null rather than True or False. Use consistently formatted cells, especially for the criteria cell.
Does the function count bold text applied by conditional formatting?
No. It compares the Font.Bold property and does not check the displayed formatting produced by conditional formatting.
Why does the count stay the same after I change bold formatting?
Formatting changes alone may not trigger recalculation. In Excel, press Ctrl+Alt+F9 to force a full recalculation.




