Filter cells in stead of full rows by color

Hi,

I have an excel that contains a set of columns with values where in each column each cell has a certain fill color. Now I want to see per column only the cells with a specific color e.g. red. I tried the filter option on the columns, but that filters complete rows. I only want to filter the cells in a column.

So in the image below I want to see only the red cells. So in column B no cells, in column C C3, in column D D4 and D5. The regular filter option would show B3 and B4 as well, because it would show the full row for C3 and the full row for D4.

Any ideas on how I can achieve this?

Thanks!

excel

Formulas and Functions

NikolinoDE

Dec 01, 2025

Here’s a clean way to do exactly what you want on a separate sheet: generate a list containing only the colored cells, per column, ready to print — without keeping the full rows.

Below is a simple VBA macro that:

Loops through a source sheet (your original data)
Goes column by column
Copies only the cells of a chosen color (e.g., red)
Places them in a destination sheet, one column at a time
Produces a compact, printable list with no gaps

No row-level filtering needed.

VBA Script: Extract Only Colored Cells Into a New Sheet

The color-detection function

(You already have this, but included for completeness.)

Function CellColorIndex(rng As Range) As Long
    CellColorIndex = rng.Interior.ColorIndex
End Function

The extraction macro

This script copies only cells of a chosen color (targetColorIndex) from each column in a source sheet, and outputs them neatly in another sheet.

Sub ExtractColoredCells()
    Dim src As Worksheet, dst As Worksheet
    Dim lastRow As Long, lastCol As Long
    Dim r As Long, c As Long, outRow As Long
    Dim targetColorIndex As Long
    
    '--- SETTINGS ---
    Set src = ThisWorkbook.Sheets("Sheet1")   ' your data sheet
    Set dst = ThisWorkbook.Sheets("Sheet2")   ' output sheet
    dst.Cells.Clear                            ' start fresh
    
    targetColorIndex = 3   ' <<=== example: 3 = red (change to your color)
    
    lastCol = src.Cells(1, src.Columns.Count).End(xlToLeft).Column
    
    ' Loop columns
    For c = 1 To lastCol
        lastRow = src.Cells(src.Rows.Count, c).End(xlUp).Row
        outRow = 1
        
        ' Write column header
        dst.Cells(1, c).Value = src.Cells(1, c).Value
        outRow = outRow + 1
        
        ' Loop rows in this column
        For r = 1 To lastRow
            If src.Cells(r, c).Interior.ColorIndex = targetColorIndex Then
                dst.Cells(outRow, c).Value = src.Cells(r, c).Value
                outRow = outRow + 1
            End If
        Next r
    Next c
    
    MsgBox "Done! Colored cells extracted."
End Sub

Hope this helps you.

5 Replies

m_tarler
Bronze Contributor
Nov 22, 2025
ok i hate this forum software. I replied yesterday and the reply disappeared. I also stated you cannot filter individual columns as it will hide the whole row. But you CAN sort by color (so another option to add to Niko's options). so the 'red' cells are at the top. You can highlight individual columns and select SORT, when the pop up asks if you want to expand or only apply to the range selected, choose only the range selected. Then you can choose to sort by color (make sure the checkbox for my data has headers is set correctly). IF you set each column as its own table (home -> format as table) then you can do this using the quick drop down arrows but you must format each column as a its own table to use that option.
NikolinoDE
Platinum Contributor
Nov 22, 2025
Excel cannot hide individual cells or “collapse” them so that only colored cells remain visible while others in the same column disappear.
Filtering always operates at the row level, never the cell level.

But there are workarounds that let you display only the cells of a specific color per column.

A - Workaround
Use a helper column to mark colored cells
This works if you need a reliable, filterable method.
1. Add a helper column next to each data column
Example: For column C, add column H labelled “C-red?”.
2. Use a formula + VBA function to detect color
Excel formulas cannot detect cell color directly, but a tiny VBA function can:
Step A — Insert this small VBA function
Press ALT+F11
Insert → Module
Paste:
Function CellColorIndex(rng As Range) As Long CellColorIndex = rng.Interior.ColorIndex End Function
Close the editor.
Step B — In your helper column enter:
=CellColorIndex(C2)
Copy down.
Step C — Filter the helper column by the color index of red
You will now see only the cells in that column that have the matching fill color.

B - Workaround
This lets you jump through only the colored cells, but not hide the others.
Steps:
Press Ctrl + F
Click Options
Click the Format… button → Choose Choose Format From Cell
Click a red cell
Click Find All
You’ll get a list of only the red cells.
Clicking any result will select that cell.
Rows are not hidden — this is navigation only, not filtering.

Hope this helps you.
- joboss
  Copper Contributor
  Nov 23, 2025
  Great, thanks for your replies.
  The sorting works quick, but ultimately I want to be able to print only the red cells, so I would still need to remove the orange cells manualy.
  The solution with the helper column still filters complete rows. And it seems to do an AND, so if I filter on two helper columns only the rows with red cells in both are shown. But maybe I can use this approach for a VBA script on a separate sheet. Basically looping over the columns and then the rows and checking the cell color. The script shouldn't be that hard now with the CellColorIndex function, I just need to figure out how to loop over columns and rows and refer to cells in other sheets, but that should also be fairly straightforward.
  - NikolinoDE
    Platinum Contributor
    Dec 01, 2025
    Here’s a clean way to do exactly what you want on a separate sheet: generate a list containing only the colored cells, per column, ready to print — without keeping the full rows.
    Below is a simple VBA macro that:
    Loops through a source sheet (your original data)
    Goes column by column
    Copies only the cells of a chosen color (e.g., red)
    Places them in a destination sheet, one column at a time
    Produces a compact, printable list with no gaps
    No row-level filtering needed.
    
    VBA Script: Extract Only Colored Cells Into a New Sheet
    The color-detection function
    (You already have this, but included for completeness.)
    Function CellColorIndex(rng As Range) As Long CellColorIndex = rng.Interior.ColorIndex End Function
    
    The extraction macro
    This script copies only cells of a chosen color (targetColorIndex) from each column in a source sheet, and outputs them neatly in another sheet.
    Sub ExtractColoredCells() Dim src As Worksheet, dst As Worksheet Dim lastRow As Long, lastCol As Long Dim r As Long, c As Long, outRow As Long Dim targetColorIndex As Long '--- SETTINGS --- Set src = ThisWorkbook.Sheets("Sheet1") ' your data sheet Set dst = ThisWorkbook.Sheets("Sheet2") ' output sheet dst.Cells.Clear ' start fresh targetColorIndex = 3 ' <<=== example: 3 = red (change to your color) lastCol = src.Cells(1, src.Columns.Count).End(xlToLeft).Column ' Loop columns For c = 1 To lastCol lastRow = src.Cells(src.Rows.Count, c).End(xlUp).Row outRow = 1 ' Write column header dst.Cells(1, c).Value = src.Cells(1, c).Value outRow = outRow + 1 ' Loop rows in this column For r = 1 To lastRow If src.Cells(r, c).Interior.ColorIndex = targetColorIndex Then dst.Cells(outRow, c).Value = src.Cells(r, c).Value outRow = outRow + 1 End If Next r Next c MsgBox "Done! Colored cells extracted." End Sub
    
    Hope this helps you.

Forum Discussion

Filter cells in stead of full rows by color

5 Replies

Resources