How to Sum Bold or Highlighted Cells in Excel using VBA
Question details
The user needs a method to calculate the sum of cells based on specific visual formatting, specifically font bolding or manual cell highlight colors.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Totaling numerical values across one or more ranges where standard formulas fail because they do not recognize manual cell formatting.
- Observed behavior
- Native Excel functions like SUMIF do not evaluate font bolding or highlight colors, requiring the implementation of custom VBA macros to achieve the goal.
Ensure that the Developer tab is enabled in your Excel ribbon so you can access the Visual Basic Editor, and be prepared to save your file as an Excel Macro-Enabled Workbook (.xlsm).
Create Custom VBA Functions for Bold and Highlighted Cells
Use custom User Defined Functions (UDFs) via VBA to sum cells dynamically based on bold text or background highlight colors.
Since native Excel functions like SUMIF cannot filter data based on formatting, writing custom VBA macros provides a flexible workaround. You can write custom functions that behave just like native formulas to sum cells across single or multiple ranges.
Navigate to the Developer tab on your Excel ribbon and click on 'Visual Basic', or simply press ALT + F11 on your keyboard.
In the Visual Basic Editor, click on 'Insert' in the top menu and select 'Module'. This will create a blank space for your custom code.
Define your custom functions (e.g., SumBold or SumHighlighted) by writing the VBA code to iterate through the given range and add up the values if the cell's Font.Bold property is True or if its Interior.Color matches your target.
Return to your Excel worksheet. You can now use your custom formulas by typing =SumBold(A2:A7, C2:C7) or =SumHighlighted(C2:C7) into any cell.
To ensure your VBA functions are not lost, go to File > Save As, and change the file format to Excel Macro-Enabled Workbook (*.xlsm).
Use WPS Spreadsheet to Run VBA Macros and Sum Formatted Cells
WPS Office Spreadsheet provides excellent compatibility with Microsoft Excel, including robust support for VBA macros. You can easily create, edit, and execute custom functions like SumBold directly within the WPS environment.
- 1. Download and Open WPS Office: Launch WPS Spreadsheet and open your existing data file.
- 2. Access the Developer Tools: Navigate to the Developer tab on the ribbon and click on 'Visual Basic' to launch the VBA editor.
- 3. Insert Macro Code: Click Insert > Module, paste your custom VBA code for summing bold or highlighted cells, and save the document as a macro-enabled file.
- 4. Calculate Your Totals: Go back to your sheet and type =SumBold() or =SumHighlighted() to easily sum your formatted data.
Frequently Asked Questions
Can these custom VBA functions process multiple non-adjacent ranges?
Yes. If the VBA function is written using a ParamArray in its arguments, it can accept multiple non-adjacent ranges, allowing you to use a formula like =SumBold(A2:A7, C2:C7, E12).
Why do I get a #NAME? error when using the SumBold formula?
This error typically occurs if macros are disabled in your spreadsheet settings or if the workbook was saved as a standard .xlsx file, which strips out the VBA code. Ensure you save the file as a Macro-Enabled Workbook (.xlsm).
Will the macro automatically update if I change a cell's highlight color?
Changing a cell's formatting does not automatically trigger a formula recalculation in Excel. To update the sum after changing colors or bolding, you will need to force a recalculation by pressing F9.
