sodcalculator

10 Tech How-To

Count Colored Cells in Excel: Fill & Conditional Formatting

Guide 10-55155 · Updated · Step by step

This guide shows you how to count cells based on their background fill color, including cells colored manually and those colored by conditional formatting rules. You’ll learn three methods: using the built-in Find feature, writing a simple VBA function, and using a filter with SUBTOTAL. No prior coding experience is needed for the first two methods.

What You’ll Need

  • Microsoft Excel (2010 or later for the Find method; any version for VBA)
  • A worksheet containing colored cells
  • For VBA: access to the Visual Basic Editor (Alt+F11) and permission to save the file as a macro-enabled workbook (.xlsm)
  • Basic familiarity with Excel’s ribbon and formula bar

Step-by-Step

Method 1: Using Find and Select (No Formulas)

  1. Press Ctrl+F to open the Find and Replace dialog.
  2. Click the Options button to expand the dialog.
  3. Click the Format button next to the “Find what” field.
  4. In the Find Format dialog, go to the Fill tab.
  5. Choose the color you want to count. If you’re unsure of the exact color, click Choose Format From Cell and select a cell with that color.
  6. Click OK to close the Find Format dialog.
  7. In the main Find and Replace dialog, click Find All.
  8. At the bottom of the dialog, Excel will display “X cell(s) found” – that’s your count.

Method 2: Using a VBA Function (Counts Any Fill Color)

  1. Press Alt+F11 to open the Visual Basic Editor.
  2. Go to Insert > Module to create a new module.
  3. Copy and paste the following code into the module:
Function CountColor(rng As Range, colorCell As Range) As Long
    Dim cell As Range
    Dim targetColor As Long
    targetColor = colorCell.Interior.Color
    For Each cell In rng
        If cell.Interior.Color = targetColor Then
            CountColor = CountColor + 1
        End If
    Next cell
End Function
  1. Close the editor and return to Excel.
  2. In a cell, type =CountColor(A1:A10, B1) where A1:A10 is your range and B1 is a cell that has the color you want to count. Press Enter.
  3. The result is the number of cells in the range that match B1’s fill color.

Method 3: Using Filter and SUBTOTAL (For Conditional Formatting)

  1. Select your data range and press Ctrl+Shift+L to add filter arrows.
  2. Click the filter arrow on the column that contains the colored cells.
  3. Choose Filter by Color and select the color you want (this works for both manual and conditional formatting colors).
  4. In a cell below the filtered data, enter =SUBTOTAL(3, A2:A100) where A2:A100 is the column range. The 3 tells Excel to count visible cells.
  5. The result shows the count of visible (filtered) cells. To get the count for all colors, repeat for each color.

Common Mistakes

  • Using COUNTIF with color criteria – COUNTIF cannot evaluate cell colors; it only works with values.
  • Forgetting to save as .xlsm – If you use VBA, the function will disappear if you save as a regular .xlsx file.
  • Mismatched colors – Manual fills and conditional formatting may appear the same but have slightly different RGB values. Use “Choose Format From Cell” to be precise.
  • Counting hidden rows – The Find method counts all cells, including hidden ones. If you only want visible, use the filter method.

Tips

  • For conditional formatting, the Find method works if you use “Choose Format From Cell” on a formatted cell – it picks up the conditional format’s color.
  • To count multiple colors at once, create a helper column with a formula like =GET.CELL(38, A1) (requires defining a name) – but this is advanced; the VBA method is simpler.
  • If you need to count colors dynamically as you change formatting, use the VBA function in a worksheet that recalculates automatically.
  • Use the Name Manager to create a defined name for the range you’re counting – it makes formulas easier to read.

Troubleshooting

  • VBA function returns #NAME? – This means the macro isn’t available. Ensure the file is saved as .xlsm and macros are enabled.
  • Find All shows 0 cells – The color you picked may not exactly match. Use “Choose Format From Cell” and re-try.
  • Conditional formatting colors don’t appear in Find – Some versions of Excel don’t recognize conditional formatting in Find. Use the filter method instead.
  • SUBTOTAL returns 0 – Make sure the range in SUBTOTAL includes the header row or at least one visible cell. Also check that the filter is actually applied.
  • VBA function counts cells with any color – If you want to count cells that are not white, modify the code to check cell.Interior.ColorIndex <> xlNone.

Frequently Asked Questions

Can I count colored cells without VBA?

Yes. The Find and Select method works without VBA, and the Filter by Color + SUBTOTAL method also works for both manual and conditional formatting colors.

Does the Find method work with conditional formatting?

Often yes, but not always. If you use “Choose Format From Cell” on a conditionally formatted cell, Excel usually picks up the color. If it doesn’t, use the filter method.

Why does my VBA function count cells with a different color?

This happens when the color you’re comparing against has a slightly different RGB value. Use the exact cell that has the color, or ensure your color is from the standard palette.

Can I count cells based on font color instead?

Yes. Modify the VBA code to use cell.Font.Color instead of cell.Interior.Color. The Find method also has a Font tab for this.

How do I count cells with multiple colors at once?

Use the VBA function with a helper column: create a list of colors, then use COUNTIF on the helper column that returns the color index for each cell. This requires a more advanced macro.

Will the VBA function slow down my workbook?

For large ranges (tens of thousands of cells), it can be slow. Limit the range to the used area and consider using the filter method for large datasets.

Related

General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.

More in this bed

The whole Tech How-To bed →