VBA & Macro Problems

How to Count Excel Cells by Color and Date After Today Using VBA

Adam Davis Oct 21, 2026 868 views

Question details

The user needs to count the number of cells in a spreadsheet that have a specific fill color and contain corresponding dates occurring later than the current day.

How to Count Excel Cells by Color and Date After Today Using VBA

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Filtering or tallying project data where visual color-coding and future date conditions are both required criteria.
Observed behavior: A custom VBA user-defined function is needed to simultaneously evaluate the cell's background color against a sample color and verify if the paired date value is strictly greater than the current system date.

Before you start

Ensure that your target date range contains actual Excel date values rather than text strings, and verify that the Developer tab is enabled on your ribbon to access the VBA editor.

Solution 1Recommended

Use a Custom VBA Function to Count by Color and Future Date

Create a User-Defined Function (UDF) in VBA that iterates through your ranges to check both the background color and the date condition simultaneously.

Standard Excel formulas do not natively read background colors. This VBA solution bridges the gap by comparing each cell in your target range against a sample color cell while simultaneously checking if the corresponding date in another column is greater than today's date.

Open the VBA Editor

Press Alt + F11 to open the Visual Basic for Applications (VBA) Editor. From the top menu, click on Insert and select Module to create a new blank script window.

Paste the VBA Code

Copy and paste the following code into the module: Function GetColorCount2(CountRange As Range, CountColor As Range, DateRange As Range) Dim CountColorValue As Long Dim TotalCount As Integer Dim i As Long CountColorValue = CountColor.Interior.Color For i = 1 To CountRange.Count If CountRange(i).Interior.Color = CountColorValue And DateRange(i).Value > Date Then TotalCount = TotalCount + 1 End If Next i GetColorCount2 = TotalCount End Function

Apply the Custom Formula in Your Worksheet

Return to your spreadsheet. Assuming your colored cells are in A2:A100, the sample color is in K1, and the dates are in D2:D100, click on an empty cell and enter the formula: =GetColorCount2(A2:A100, K1, D2:D100). Press Enter to view the count.

Use a Custom VBA Function to Count by Color and Future Date

Range Alignment: Ensure the CountRange (colors) and DateRange (dates) contain the exact same number of cells (e.g., rows 2 through 100) to prevent errors or miscalculations.

Count Cells by Color and Date Seamlessly in WPS Spreadsheet

WPS Spreadsheet fully supports VBA and Macros, allowing you to implement custom functions like counting by color and date. You can easily manage complex datasets with custom formatting using a familiar, lightweight interface.

1. Open WPS Spreadsheet: Launch WPS Spreadsheet and open your existing workbook containing the color-coded data and dates.
2. Access the Macro Editor: Navigate to the Tools tab on the ribbon, click on 'Developer' or 'Macros', and open the Visual Basic Editor.
3. Insert the Function: Insert a new Module, paste the GetColorCount2 VBA code, and use the formula directly in your sheet just as you would in standard Excel.

Full support for standard VBA macros and custom user-defined functionsSeamless compatibility with Microsoft Excel (.xlsx and .xlsm) macro-enabled filesLightweight application with high performance for massive datasetsCompletely free to download with built-in advanced formula capabilities

microsoft office alternative - wps office

Frequently Asked Questions

Why is the VBA function returning a #VALUE! error?

This typically occurs if the range sizes for the colored cells and the date cells do not match. Ensure that both ranges have the exact same number of rows and columns, such as A2:A100 and D2:D100.

Does the VBA function update automatically when I change a cell's color?

No, changing a cell's background color does not trigger a standard worksheet recalculation in Excel. You must press the F9 key or double-click the formula cell and press Enter to force the sheet to recalculate and update the count.

Can I modify the VBA code to include today's date instead of strictly after today?

Yes. In the VBA script, locate the condition 'DateRange(i).Value > Date' and change the greater-than sign (>) to greater-than-or-equal-to (>=) so it reads 'DateRange(i).Value >= Date'. This will include today's date in the final count.

Why isn't the date condition working for some cells?

The custom function relies on valid date formats. If your dates are accidentally stored as text strings instead of actual serial date numbers, the greater-than-date comparison will skip them. Convert those cells to proper dates using the 'Text to Columns' feature or the DATEVALUE function.

Excel & Data Problems