Count Cells by Fill Color and Write the Result to an Excel Cell
Question details
The user wants a VBA color count written to a worksheet cell rather than displayed in a message box.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A macro counts cells in a range whose fill color matches a reference cell.
- Observed behavior
- The count is displayed in a message box instead of being written to a worksheet cell.
Identify the range to count, the reference cell, and the destination cell. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if it is not already in a macro-enabled format, and enable macros only if you trust the file.
Write the VBA color count to a worksheet cell
Replace the message box with a cell assignment to put the existing count on the worksheet.
The macro must calculate color_count before assigning it to the destination cell. Standard worksheet formulas generally cannot read a cell's Interior.Color property directly.
Open the workbook in desktop Excel and press Alt+F11 to open the Visual Basic Editor. In the Project pane, open the module containing your color-counting macro.
Find the MsgBox statement that displays color_count and replace it with: ThisWorkbook.Worksheets("Sheet1").Range("F1").Value = color_count. Change Sheet1 to the destination worksheet name and F1 to the cell where you want the result.
Return to Excel, press Alt+F8, select the macro, and click Run. Check the destination cell for the count.

Review Color-Coded Spreadsheets in WPS Office
WPS Office is a free, lightweight Office alternative with a familiar spreadsheet interface and support for common Excel formats, making it easy to move everyday spreadsheet work between applications. It does not fix an Excel VBA macro; macro support can vary by WPS edition and platform.

Frequently Asked Questions
How do I put the count on a different worksheet?
In the VBA assignment, replace Sheet1 with the destination sheet name and F1 with the desired cell, for example: ThisWorkbook.Worksheets("Results").Range("B2").Value = color_count.
Will the result update when I change a cell's fill color?
The cell assignment writes the count at the time the macro runs. After changing fill colors, press Alt+F8 in desktop Excel and run the macro again.
Does Interior.Color count colors from conditional formatting?
Interior.Color reads the underlying fill, which may not match the displayed conditional-formatting color. A macro designed to inspect displayed formatting is needed for that case.
Why do I need to save the workbook as an .xlsm file?
The .xlsm format retains VBA macros. In Excel, use File > Save As and choose Excel Macro-Enabled Workbook to save the workbook in that format.




