Hide/filter rows or columns based on selected cell color

[pholt33]

Excel's built-in "filter by selected cell color" is really helpful but also limited. How can I do the opposite... filter out or hide rows based on the selected cell color. I found the below in an old post on filtering based on a specific color. Is there a way to modify that or is there a different code that can do what I am asking?

Option Explicit

Sub test()
Dim LR As Long      'Last Row
Dim RN As Integer   'Row Number

LR = Cells(Rows.Count, "J").End(xlUp).Row

Application.ScreenUpdating = False

    For RN = 1 To LR
    If Cells(RN, "J").Interior.ColorIndex = 6 Then Cells(RN, "J").EntireRow.Hidden = True
    Next RN
    
 Application.ScreenUpdating = True
 
End Sub

I modified the above to <> 6 and that worked fine. Then I tried this without success:

    For RN = 1 To LR
        If Cells(RN, "A").Interior.ColorIndex = ActiveCell.Interior.Color Then _
                Cells(RN, "A").EntireRow.Hidden = True
    Next RN

Also, the above is specific to J:J, how can I make it work for any column?

Thanks

 

[John_w]

Like this:

Public Sub Test()

    Dim LR As Long 'last Row
    Dim LC As Long 'last column
    Dim R As Long, C As Long
    
    LR = Cells(Rows.Count, "J").End(xlUp).Row
    LC = Cells(1, Columns.Count).End(xlToLeft).Column
    
    Application.ScreenUpdating = False
    
    For R = 1 To LR
        For C = 1 To LC
            If Cells(R, C).Interior.ColorIndex = ActiveCell.Interior.ColorIndex Then
                Cells(R, C).EntireRow.Hidden = True
                Exit For
            End If
        Next
    Next
    
    Application.ScreenUpdating = True
 
End Sub