VBA & Macro Problems

How to Use VBA to Sum Only Bold Numbers in Excel

Tauseeq Magsi Oct 1, 2026 869 views

Question details

The user needs a VBA macro to calculate the sum of numeric cells that are formatted with bold font within a specified Excel range.

How to Use VBA Code to Sum Only Bold Numbers in Excel

Product: Excel
Device & OS: not provided
Scenario: Automating the summation of specific cells based on their font formatting rather than just their values.
Observed behavior: The goal is to loop through a selected range, identify which cells contain numeric values and bold text, and calculate their total.

Before you start

Ensure that the Developer tab is enabled in your Excel ribbon and that you have saved your workbook as an Excel Macro-Enabled Workbook (.xlsm) to preserve your VBA code.

Solution 1Recommended

Create a Custom VBA Macro to Sum Bold Numbers

Use a custom VBA function that iterates through a specified range, checks the bold font property, and sums the values of matching cells.

This method uses a simple For Each loop in VBA to check the Font.Bold property of each cell. It ensures that only cells containing numeric values are added to the total, preventing errors from text entries. Once created, you can use this macro just like a standard Excel formula.

Open the VBA Editor

Press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) Editor in Excel.

Insert a New Module

Click on 'Insert' in the top menu bar of the VBA editor and select 'Module' to create a new blank workspace for your code.

Paste the VBA Code

Copy and paste the following code into the module window: Function SumBold(rng As Range) As Double Dim cell As Range Dim total As Double For Each cell In rng If cell.Font.Bold And IsNumeric(cell.Value) Then total = total + cell.Value End If Next cell SumBold = total End Function

Use the Custom Formula

Close the VBA Editor and return to your Excel worksheet. Select an empty cell and type the formula =SumBold(A1:A100) (replace A1:A100 with your actual target range), then press Enter to calculate the total.

Create a Custom VBA Macro to Sum Bold Numbers

Recalculation Note: Custom VBA functions based on cell formatting do not automatically recalculate when you change a cell's format to bold. You will need to press F9 on your keyboard to force the worksheet to recalculate the formula.

Automate with WPS Office

Use WPS Spreadsheet to Run VBA Macros Easily

WPS Spreadsheet provides robust support for VBA macros, allowing you to automate tasks like summing bold numbers exactly as you would in Microsoft Excel. It offers a highly familiar interface and full compatibility with macro-enabled files.

1. Enable the Developer Tools: Open WPS Spreadsheet, go to the options menu, and ensure the Developer tab is enabled to access macro tools.
2. Open the VBA Editor: Navigate to the Developer tab and click 'Macros', or simply press ALT + F11 to launch the built-in VBA Editor.
3. Insert Code: Click Insert > Module, and paste the SumBold custom function code into the blank module.
4. Calculate Totals: Return to your WPS Spreadsheet and type =SumBold(Range) in any cell to instantly sum your bold numeric values.

Highly compatible with Microsoft Excel formats, including Macro-Enabled Workbooks (.xlsm).Built-in VBA editor for writing, editing, and debugging custom macros and functions.Familiar user interface that requires no learning curve to master.Lightweight software that opens large datasets and runs macros smoothly.

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't the sum update automatically when I make a new number bold?

Excel formulas do not trigger automatic recalculations based purely on formatting changes (like changing text to bold). To update the total sum after applying new bold formatting, you must press the F9 key to manually recalculate the entire worksheet.

Can I sum cells based on other formatting, like background color?

Yes, you can easily modify the VBA code to check for different cell properties. For example, instead of checking 'cell.Font.Bold', you could check 'cell.Interior.ColorIndex' to sum cells based on specific background colors.

Will this macro return an error if there is text in a bolded cell?

No, the provided VBA code includes an 'IsNumeric(cell.Value)' condition. This ensures that the macro only adds up cells containing valid numbers, completely ignoring text entries and preventing type mismatch errors.

How do I save a workbook containing this VBA code?

To preserve your custom VBA macro for future use, you must save your file as an Excel Macro-Enabled Workbook. Go to File > Save As, and choose '.xlsm' from the file format dropdown menu.

Excel & Data Problems