VBA & Macro Problems

How to Count Yellow Cells and Add Time Using VBA in Excel

WPS Editor Oct 9, 2026 869 views

Question details

The user needs to count cells in Excel based on their yellow background color and add a specific time value for each matching cell.

How to Count Yellow Cells and Add Time Using VBA in Excel

Product: Excel
Device & OS: not provided
Scenario: Tracking time or scheduling where specific time blocks are visually marked with a yellow background color.
Observed behavior: Standard Excel formulas cannot count cells by background color, requiring a custom VBA function to achieve the goal.

Before you start

Ensure your Excel workbook is saved as an Excel Macro-Enabled Workbook (.xlsm) to allow custom VBA scripts to run properly.

Solution 1Recommended

Create and Use a Custom VBA Function

Since standard formulas do not support color counting, you can write a short VBA script to loop through a range, identify yellow cells, and use a custom formula to calculate the added time.

Excel does not have a built-in formula to count cells by their background color. By using the Visual Basic Editor, we can create a User Defined Function (UDF) that checks the Interior.Color property of each cell.

Open Visual Basic Editor

Press Alt + F11 on your keyboard to launch the Visual Basic Editor in Excel.

Create a New Module

In the top menu, navigate to Insert > Module to open a new blank coding window.

Write the CountYellow Function

Write a VBA function named CountYellow that loops through a selected range and increments a counter if a cell's Interior.Color matches the code for yellow (such as vbYellow).

Apply the Custom Formula

Return to your Excel worksheet. In your target cell, enter your time calculation formula incorporating the new function. For example: =IF(OR($N$10="",O13=""),"",O13*$N$10+CountYellow(D13:N13)).

Manually Recalculate

Press F9 on your keyboard to manually recalculate the sheet whenever you change a cell's color, ensuring your added time values update correctly.

Recalculation Requirement: Excel formulas do not automatically recalculate when only a cell's formatting (like background color) changes. You must press F9 to refresh the calculations.

Advanced Spreadsheets with WPS Office

Use WPS Spreadsheet for Your Macro and VBA Needs

WPS Office offers robust support for VBA and macros, allowing you to easily create custom functions like counting colored cells. Enjoy a familiar interface and comprehensive formula support.

1. Install WPS Office: Download and install WPS Office, then open your .xlsm file in WPS Spreadsheet.
2. Open the Macro Editor: Navigate to the Developer tab or press Alt+F11 to access the built-in VBA Macro Editor.
3. Run Your Custom Function: Insert a new module, write your CountYellow script, and use the custom formula directly in your worksheet just as you would in Excel.

Full compatibility with Microsoft Excel (.xlsx, .xlsm, .csv) formats.Supports VBA macros to automate repetitive tasks and create custom functions.Lightweight and runs smoothly on all major operating systems.Free and easy-to-use alternative with a highly familiar user interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't the CountYellow function update immediately when I color a new cell yellow?

Changing a cell's background color does not trigger Excel's automatic recalculation engine. You need to press the F9 key to manually force Excel to recalculate the custom function and update your added time.

How do I find the correct color code for yellow in VBA?

In VBA, standard yellow can be referenced using the constant vbYellow or the numeric value 65535. You can also use the Interior.Color property of a known yellow cell to find its specific code in the Immediate Window.

Can I use standard Excel formulas to count colored cells instead of VBA?

No, standard Excel functions like COUNTIF do not have the ability to evaluate cell formatting or background colors. A custom VBA function (UDF) is required to check the formatting attributes.

Excel & Data Problems