Count Blank Cells by Fill Color in Excel VBA
Question details
Count cells that are empty and have the same fill color as a reference cell by using a custom VBA function.
- Product
- Microsoft Excel VBA
- Device & OS
- not provided
- Scenario
- The user needs a worksheet function that counts blank cells within a selected range according to a specified cell fill color.
- Observed behavior
- A standard color-counting function must be updated so it excludes nonblank cells, even when they use the matching fill color.
Save the workbook as an Excel Macro-Enabled Workbook (*.xlsm) before adding VBA code. Use a normally empty reference cell with the fill color you want to count; cells containing formulas that return an empty string are not considered empty by IsEmpty.
Add an IsEmpty check to the VBA color-counting function
Use this method when you want a custom function that counts only truly blank cells whose fill color matches a reference cell.
The function compares each cell's Interior.Color with the reference cell's color and counts it only when IsEmpty returns True. Because Excel does not automatically recalculate user-defined functions after a fill-color-only change, recalculate the worksheet when you update colors.
In Excel, press Alt+F11, then select Insert > Module from the Visual Basic Editor menu.
Paste this code into the new module: Function CountBlankByColor(rngToCount As Range, refColor As Range) As Long Dim cellCurrent As Range For Each cellCurrent In rngToCount If cellCurrent.Interior.Color = refColor.Interior.Color And IsEmpty(cellCurrent) Then CountBlankByColor = CountBlankByColor + 1 End If Next cellCurrent End Function
Select File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm), then close the Visual Basic Editor.
Enter a formula such as =CountBlankByColor(A2:A100,D1), where A2:A100 is the range to inspect and D1 is a blank cell with the target fill color.
After changing a cell fill color, press F9 or use Formulas > Calculate Now so the custom function updates its result.
Count Color-Coded Blank Cells with VBA in WPS Spreadsheet
WPS Spreadsheet provides a familiar workbook environment for creating macro-enabled spreadsheets and using VBA-based logic to count blank cells by fill color. Add the custom function in the VBA editor, save the file in a macro-enabled format, and use the function directly in your worksheet.
- 1. Open the workbook in WPS Spreadsheet: Launch WPS Office, choose Spreadsheets, and open the workbook that contains the colored cells.
- 2. Open the VBA editor: Use the Developer tools to open the Visual Basic Editor, then choose Insert > Module.
- 3. Insert the color-counting function: Paste the CountBlankByColor function into the module and save the workbook as an .xlsm file.
- 4. Enter the worksheet formula: Select an output cell and enter =CountBlankByColor(A2:A100,D1), replacing the range and reference cell with your own cells.
Frequently Asked Questions
Why does my color-counting VBA function include cells that contain values?
Make sure the If statement includes both conditions: cellCurrent.Interior.Color = refColor.Interior.Color And IsEmpty(cellCurrent). Without IsEmpty(cellCurrent), every cell with the matching fill color is counted.
Can I count cells with formulas that display as blank?
Yes. Replace IsEmpty(cellCurrent) with Len(cellCurrent.Value)=0 in the VBA function. This includes cells with a formula that returns an empty string, such as ="".
Why does the count not change after I change a cell's fill color?
Excel user-defined functions do not reliably recalculate from formatting-only changes. Press F9, select Formulas > Calculate Now, or edit the formula cell to force recalculation.
What should I use as the reference color cell?
Use a cell with the exact fill color you want to count. In a formula such as =CountBlankByColor(A2:A100,D1), cell D1 supplies the target Interior.Color.
Why must the workbook be saved as an .xlsm file?
The .xlsm format retains VBA modules and macro code. If you save the workbook as .xlsx, Excel removes the custom VBA function.
