Grouped rows, hide/reveal base on cell value

Hi,

I'm looking to expand/hide rows that have been grouped (Data tab, Outline section, Group). What I really want is to be able to group rows, and name them because I have multiple groups I need control over. Then have them expand or hide based on a cell value. For example I want to group row 10 to 20, and if b6 = "Blue" have the rows expand if not have them hidden. Hopefully someone can help me with this I'm not having any luck with google.

Thanks
Dave

Upload sample file please (with innocuous/sanitized data).

Okay, it's not much to see, but here you go.

Are you manually grouping or using Subtotal grouping?

I ask, since it's easier to hide rows if there isn't Subtotal or other calculation done for each grouping.

Edit: Oh and is there any logic for your grouping? I'll need better representation of your logic and format.

I was manually grouping the rows no need for calculations.

Ah as for logic I have multiple products, so I was going to create a data table with the product name and a value either a 1 or 0. Then use a vlookup to determine the value, and from that value determine if the group remains compressed or is expanded.

Here's what I did with your sample. It's only starting point. You may want to modify as needed.

Set up:
1: Data validation source range: S2:S5 (S5 for blank)
2: Added empty row after each grouping (needed to break each groups)
3: Formula in T2:T4 to find empty rows in column (each break).
In T2. Confirmed as array (CTRL + SHIFT + ENTER). Copied down.

=SMALL(IF($C$11:$C$43="",ROW($C$11:$C$43)),ROW(A1))

4: In D3 (beside validation cell) following formula

=VLOOKUP(C3,S2:T4,2,0)

5: In standard module following code. Rows(#).ShowDetail is used to control individual group.

Sub ShowHideGroup(ws As Worksheet, rVal As Long)
Dim i As Integer
myArray = ws.Range("T2:T" & ws.Cells(Rows.Count, "T").End(xlUp).Row)

For i = 1 To UBound(myArray, 1)
    If myArray(i, 1) = rVal And ws.Rows(myArray(i, 1)).ShowDetail <> True Then
        ws.Rows(rVal).ShowDetail = True
    ElseIf myArray(i, 1) <> rVal And ws.Rows(myArray(i, 1)).ShowDetail = True Then
        ws.Rows(myArray(i, 1)).ShowDetail = False
    End If
Next i

End Sub

6: In Worksheet module (for sheet1 in this case) Me = Activesheet, RowLevels:=2 will expand all, :=1 will collapse all.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C3")) Is Nothing Then
    If Target.Value = "" Then
        Me.Outline.ShowLevels RowLevels:=2
    Else
        Call ShowHideGroup(Target.Parent, Target.Offset(, 1).Value)
    End If
End If
End Sub

See attached sample.