AutoFilter Column based on more than one color

[alsharif]

Hi brothers,

Need your urgent help in correcting the below code to be able to use it to filter on column based on multiple colors:

ActiveSheet.Range("A1").AutoFilter Field:=6, Criteria1:=RGB(51, 63, 79), Criteria2:=RGB(255, 242, 204), Operator:=xlFilterCellColor


the code above only filter one color I put two criteria to tilter two colors !!

Note: I do not want to use color index method .

Thanks in advance brothers

 

[vcoolio]

Hello Alsharif,

You'll find that this is another little Excel quirk with this type of functionality not offered by MS. You can only filter on one colour at a time.
One workaround that I use is to place a marker (X) in each relevant cell in a helper column, out of sight. Usually Column Z. Hence the workaround VBA code would look something like this:-

Sub Test()
      
        Dim c As Range
      
        Application.ScreenUpdating = False
      
        For Each c In Sheet1.Range("A2", Sheet1.Range("A" & Sheet1.Rows.Count).End(xlUp))
              If Not c.Interior.Color = RGB(51, 63, 79) Then
              If Not c.Interior.Color = RGB(255, 242, 204) Then
              c.Offset(, 25) = "X"
              End If
              End If
        Next c
        With Sheet1.Range("Z2", Sheet1.Range("Z" & Sheet1.Rows.Count).End(xlUp))
              .AutoFilter 1, "<>X"
        End With
      
        Application.ScreenUpdating = True

End Sub

This code assumes that you have headings in row1 with data starting in row2.
You could also hide the unwanted rows as another type of workaround.

I hope that this helps.

Cheerio,
vcoolio.

P.S. As you are filtering on column 6, you would need to change the "A2" and the "A" to "F2" and "F" in your current code. You would also need to change the Offset to suit yourself.

 

[kevin9999]

Alternative to @vcoolio 's approach that doesn't use AutoFilter or a helper column. Also assumes headers in row 1 and run from the active sheet.

Sub colors()
    Dim rng As Range, c As Range
    Application.ScreenUpdating = False
    
    For Each c In Range("F2", Cells(Rows.Count, "F").End(xlUp))
        If c.Interior.Color = RGB(51, 63, 79) _
        Or c.Interior.Color = RGB(255, 242, 204) Then
            c.EntireRow.Hidden = False
        Else
            c.EntireRow.Hidden = True
        End If
    Next c
    
    Application.ScreenUpdating = True
End Sub