excelvbafiltercolors

How to filter Not Red Cells?


The following code can filter Red Cells successfully.

Sub Macro1()
Sheets("Sheet1").Range("A1:E500").AutoFilter Field:=1, Criteria1:=RGB(255, 0, 0), Operator:=xlFilterCellColor
End Sub

I am looking for vba code which can filter Not Red Cells.

Lets say the color of A2 cell is blue.

Lets say the color of A3 cell is green.

Lets say the color of A4 cell is red.

Lets say the color of A5 cell is yellow.

I want to filter A2, A3 and A5 cells.

I dont want to use for next loop if it is possible.

I mean I want to use only AutoFilter if it is possible.


Solution

  • The script follows the same approach as Michal's answer but uses a different method to populate the helper column.

    Sub Demo()
        Dim helpRng As Range, visRng As Range
        Dim lastRow As Long, oSht As Worksheet
        Set oSht = Sheets("Sheet1")
        ' remove existing filter
        If oSht.AutoFilterMode Then oSht.AutoFilterMode = False
        lastRow = oSht.Cells(oSht.Rows.Count, 1).End(xlUp).Row
        With oSht.Range("A1:E" & lastRow)
            ' filter RED on Col A
            .AutoFilter Field:=1, Criteria1:=RGB(255, 0, 0), Operator:=xlFilterCellColor
            ' help col
            Set helpRng = .Columns(.Columns.Count).Offset(, 1)
            helpRng.EntireColumn.Hidden = False
            On Error Resume Next
            ' get the visible cells on the help col
            Set visRng = helpRng.SpecialCells(xlCellTypeVisible)
            On Error GoTo 0
            If Not visRng Is Nothing Then
                oSht.AutoFilterMode = False
                helpRng.Clear
                ' fill the helper col
                visRng.Value = 1
                ' filter blank cells on the help col
                helpRng.AutoFilter Field:=1, Criteria1:=""
                ' hide the helper col
                helpRng.EntireColumn.Hidden = True
            End If
        End With
    End Sub
    

    enter image description here