Filter to only show rows that have colored cells

Timeizmonies

New Member
Joined
Dec 26, 2014
Messages
38
Hello,

I am trying to filter my worksheet to only show rows that have a highlighted cell somewhere in the row (not necessarily in the same column). Since it could be any column I can't just do a simple filter by color because I want all possible highlighted cells to appear. Thanks.
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
How exactly are these cells colored? Is there any logic or "rhyme or reason" to it?
If so (like Conditional Formatting), it is usually better to use that same logic to determine which rows to show instead of trying to look at each cell's color.
We can look at each cell's color, but that would be a long, cumbersome loop in VBA that may be slow/inefficient.
 

Timeizmonies

New Member
Joined
Dec 26, 2014
Messages
38
I just highlighted cells I want to review later yellow, so I only need to pull rows that have columns with a yellow cell in it.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
Try this macro:
Code:
Sub MyHideMacro()

    Dim lastCell As Range
    Dim lastRow As Long
    Dim lastCol As Long
    Dim myRow As Long
    Dim myCol As Long
    Dim myHide As Boolean
    Dim myColorCode As String
    
'   Enter color code to look for
    myColorCode = vbYellow

    Application.ScreenUpdating = False

'   Unhide all rows by default
    Cells.EntireRow.Hidden = False
    
'   Find last cell with data
    ActiveWorkbook.Save
    Set lastCell = Range("A1").SpecialCells(xlLastCell)
    
'   Find last row and column
    lastRow = lastCell.Row
    lastCol = lastCell.Column
    
'   Loop through all rows
    For myRow = 1 To lastRow
'       Default hide as true
        myHide = True
'       Loop through all columns
        For myCol = 1 To lastCol
'           Check to see if any columns are highlighted
            If Cells(myRow, myCol).Interior.Color = myColorCode Then
                myHide = False
                Exit For
            End If
        Next myCol
'       Hide/not hide row
        Rows(myRow).Hidden = myHide
    Next myRow
    
    Application.ScreenUpdating = True

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,561
Messages
5,596,846
Members
414,107
Latest member
Tigretto

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top