Filter to only show rows that have colored cells

[Timeizmonies]

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.

 

[Joe4]

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]

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]

Try this macro:

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

 

[Timeizmonies]

That worked perfectly, thanks!