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

Vdoc

New Member
Joined
Dec 30, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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!



VBA Code:
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
if either two cells B7,C7 are green (10) then row 5 is shown.
For that, see if this does what you want.

VBA Code:
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
 
Solution

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Vdoc

New Member
Joined
Dec 30, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Glad we got it resolved. Thanks for letting us know. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,613
Messages
5,625,848
Members
416,139
Latest member
MattBoard

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top