VBA & Macro Problems

Fix an Excel VBA Function to Count Cells by Text and Bold Formatting

Ayan MasoodAyan Masood Nov 18, 2026 869 views

Question details

The user needs an Excel VBA function that counts cells matching both the text and bold formatting of a criteria cell.

Count Cells by Text and Bold Formatting with Excel VBA
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA editor

In desktop Excel, open the workbook and select Developer > Visual Basic. In the VBA editor, select Insert > Module.

2
Add the function

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

3
Enter the formula

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.

4
Save the macro

Select File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm). Enable macros when prompted for a workbook you trust.

Store the Criteria Cell's Bold State in a Boolean Variable
Formatting and recalculation: The function checks Font.Bold, not bold formatting displayed by conditional formatting. Changing formatting alone may not recalculate the result; press Ctrl+Alt+F9 to force a full recalculation.
Free Microsoft Office alternative

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.

Open and edit spreadsheet data in a familiar worksheet interface.Work with Microsoft Excel XLSX files for easier migration.Use spreadsheet formatting tools to organize and highlight text.Use a free, lightweight Office alternative.
microsoft office alternative - wps office

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.