VBA & Macro Problems

How to Count Colored Cells That Contain Data in Excel

Algirdas Jasaitis Oct 9, 2026 871 views

Question details

The user wants to count the number of cells that have a specific background fill color, but only if those cells actually contain data (ignoring any colored cells that are completely empty).

How to Count Colored Cells That Contain Data in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Performing data analysis or project tracking where both the cell's background color and the presence of text/values indicate a specific task status.
Observed behavior: Standard Excel functions do not offer a dependable way to count by cell color. Existing custom solutions or scripts often incorrectly include empty colored cells in the final count.

Before you start

Ensure your workbook is saved as an Excel Macro-Enabled Workbook (.xlsm) if you plan to use VBA. Additionally, verify whether your cell colors were applied manually or via Conditional Formatting, as Conditional Formatting requires a slightly different VBA approach.

Solution 1Recommended

Create a Custom VBA Function (UDF) to Count Colored Cells with Data

Since Excel lacks a built-in function to count by color, creating a User Defined Function (UDF) using VBA is the most reliable way to count cells that are both specifically colored and non-empty.

By writing a simple VBA macro, you can command Excel to loop through a specified range of cells. The code will check two conditions simultaneously: whether the cell's interior color matches your reference color, and whether the cell contains any data.

Open the VBA Editor

Press the shortcut 'Alt + F11' on your keyboard to open the Microsoft Visual Basic for Applications (VBA) Editor.

Insert a New Module

In the top menu, click 'Insert' and select 'Module'. This will create a blank script window where you can write your custom function.

Add the VBA Code

Paste a custom VBA script that accepts a data range and a color reference range. The script should use a For Each loop to check 'If cell.Interior.Color = refColor.Interior.Color And cell.Value <> "" Then' to increment your count.

Apply the Formula in Your Worksheet

Close the VBA Editor and return to your worksheet. You can now use your newly created function just like a standard Excel formula, for example: '=CountColorWithData(A1:A20, C1)' where C1 contains the target color.

Create a Custom VBA Function (UDF) to Count Colored Cells with Data

Updating Custom Function Values: Changing a cell's fill color does not automatically trigger Excel to recalculate formulas. You will need to press 'F9' to manually refresh the worksheet and update your count.

Solution 2

Filter Data by Color and Use the SUBTOTAL Function

If you are not comfortable using VBA macros, you can use a combination of Excel's Filter tool and the SUBTOTAL function to count visible colored cells that contain data.

Advanced Spreadsheet Capabilities

Easily Manage Data and Macros with WPS Spreadsheet

WPS Office provides a highly compatible spreadsheet application that seamlessly handles complex data filtering, standard formulas, and advanced calculations. For users who need custom functions, WPS Spreadsheet supports VBA macros, making it incredibly easy to automate tasks like counting colored cells.

1. Download and Install: Get WPS Office Free from the official website and install it on your device.
2. Open Your Excel File: Launch WPS Spreadsheet and open your existing .xlsx or .xlsm workbook.
3. Utilize Advanced Tools: Easily apply the SUBTOTAL and Filter-by-color method, or navigate to the Developer tab to implement your VBA macros seamlessly.

Fully compatible with Microsoft Excel formats, including .xlsx and macro-enabled .xlsm files.Native support for VBA macros allows you to easily port and run your custom color-counting functions.Free, lightweight alternative with a familiar, easy-to-navigate user interface.Includes powerful built-in filtering and data analysis tools right out of the box.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my current VBA code count empty colored cells?

Your existing VBA code is likely only verifying the 'Interior.Color' property of the cells in your range. To prevent empty cells from being counted, the VBA loop must include an additional logical check, such as 'If cell.Value <> ""', to ensure it only counts cells that actually hold data.

Can I use COUNTIF or COUNTIFS to count cells by color in Excel?

No. The built-in COUNTIF and COUNTIFS functions in Excel are designed to evaluate cell contents (such as numbers, text, or dates) and logical criteria. They cannot read or evaluate cell formatting properties like background fill colors.

Will color-counting macros automatically update when I change a cell's color?

No. Changing a cell's background color does not trigger a standard worksheet recalculation in Excel. To update the total after changing cell colors, you must force a recalculation by pressing the F9 key.

Excel & Data Problems