Hiding Columns by Text Value Working / Hiding Rows by Cell Background Color Not

[Vdoc]

Hello all,

First post so please bear with me. I have some vba code on a sheet that works well to hide columns based on text in a cell (eg. "Test1" hides columns B:P)

Trying to do the same for rows but with background color but not working and have tried so many variations just figured I'd pause and see if I could reach out to the community to help.

Thanks in advance for any ideas offered!

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Activate
If Not Application.Intersect(Range("Q4"), Range(Target.Address)) Is Nothing Then
        Select Case Target.Value
       
        Case Is = "Test0":
                  Columns("A:ZZ").EntireColumn.Hidden = False
                            'Rows("5:5").EntireRow.Hidden = False

        Case Is = "Test1":
                            Columns("Q:BH").EntireColumn.Hidden = False
                            Columns("B:P").EntireColumn.Hidden = True

        End Select
End If

If Not Application.Intersect(Range("B7"), Range(Target.Address)) Is Nothing Then
        Select Case Target.Interior.ColorIndex
    Case xlNone, 10:  Rows("5:5").EntireRow.Hidden = True
    Case Else: Rows("5:5").EntireRow.Hidden = False

End Select
End If


End Sub

 

[Peter_SSs]

if either two cells B7,C7 are green (10) then row 5 is shown.

For that, see if this does what you want.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If Target.Address(0, 0) = "B7" Or Target.Address(0, 0) = "C7" Then
    Rows(5).Hidden = Range("B7").Interior.ColorIndex <> 10 And Range("C7").Interior.ColorIndex <> 10
  End If
End Sub

 

[Vdoc]

Thank you so much for your help Peter!! To be honest I usually avoid posting questions in forums because people generally don't have the patience with me as I struggle to properly articulate my issue.

This solved my issue perfectly and after having a difficult holiday this really made a difference for me.

Appreciate all your help!

 

[Peter_SSs]

You're welcome. Glad we got it resolved. Thanks for letting us know.