none
Excel VBA: sheet records filter by multiple background colors RRS feed

  • Question

  • Hi community,

    The following is my code that works ok with one color filtering.

    Sub btnFilter_Click()
    
            Dim LastRow As Long
    
            LastRow = ActiveSheet.Range("C8").CurrentRegion.Rows.Count
    
            ActiveSheet.Range("C8:C" & LastRow).AutoFilter Field:=1, Criteria1:=RGB(255, 0 , 0), Operator:=xlFilterCellColor 
    
    End Sub

    Ok, let's say, I need this code works good for filtering multiple colors. Then, what should I do.

    I tried Criteria1:=Array(RGB(255, 0 , 0), RGB(200, 100 , 100), RGB(100, 100 , 100)), but Array does not work.

    Please advice, thanks!



    • Edited by MelZZ Friday, September 18, 2015 4:48 PM typo
    Friday, September 18, 2015 4:45 PM

Answers

  • Excel doesn't support filtering for multiple colors.

    A workaround could be to enter values corresponding to the colors in a helper column. Filtering on multiple cell values is supported.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, September 19, 2015 9:30 AM