Excel VBA: How to create custom color filter (combo box control with all option) in Excel

[marlline]

I have a need: create a combo box (or maybe something else) to filter my file by COLOR and INCLUDING ALL OPTION.

sample data listed below. Goal is when color red, orange, or yellow selected at B7 Combo Box, rows under column c will be filtered accordingly with understanding that any uncolored rows will be kepted. Also there will be an option of ALL.

Any of your suggestions will be greatly appreciated! Thank you very much!!

[ATTACH type="full"]24152[/ATTACH][CODE=xls]

[/CODE]

 

[Yongle]

Place code below in SHEET code window
It is triggered automatically when value in cell B7 is amended
Data validation list in B7 : Apple,Orange,Banana,All
Save workbook as macro enabled


Right-click on sheet tab \ select View Code \ paste code into that window

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B7")) Is Nothing Then Call HideRows(Target.Text)
End Sub

Private Sub HideRows(txt As String)
    Dim Cel As Range, Rng As Range, uRng As Range
    Set Rng = Range("C1", Range("C" & Rows.Count).End(xlUp))
    Set uRng = Range("C" & Rows.Count)
    Range("A:A").EntireRow.Hidden = False
    If txt = "All" Then Exit Sub
    Application.ScreenUpdating = False
    On Error Resume Next
    For Each Cel In Rng
        Select Case Cel.Interior.Color
            Case Range("C8:C10").Find(txt).Interior.Color, 16777215
            Case Else:: Set uRng = Union(uRng, Cel)
        End Select
    Next Cel
    On Error GoTo 0
    uRng.EntireRow.Hidden = True
    Range("C" & Rows.Count).EntireRow.Hidden = False
    On Error GoTo 0
End Sub