VBA Code - Hide / Unhide Rows based on Entity Type

[tlc53]

Hi there,

Not had to write a code like this so have taken a stab in the dark but it's not liking it. Can anyone see where I am going wrong please? When I try to run it, the error highlights the second row of code (If Not Intersect...)
The "Entity_Type" is located in cell D8. The "Entity Type" is selected on another sheet by a drop down and just transferred over to this sheet.
I'm trying to hide/unhide rows depending on whether the Entity Type is Company or not.

Thank you!

Private Sub Worksheet_Change(ByVal Target As Range)


If Not Intersect(Target, Range("Entity_Type")) Is Nothing Then


If Target.Cells.CountLarge > 1 Then Exit Sub
Select Case Target.Value


    Case Is <> "Company"
        Range("26:29:,52:88").EntireRow.Hidden = True
    Case "Company"
        Range("26:29:,52:88").EntireRow.Hidden = False
     
   End Select
   End If


End Sub

 

[Akuini]

Maybe this:

[FONT=lucida console][COLOR=Royalblue]Private[/COLOR] [COLOR=Royalblue]Sub[/COLOR] Worksheet_Change([COLOR=Royalblue]ByVal[/COLOR] Target [COLOR=Royalblue]As[/COLOR] Range)


[COLOR=Royalblue]If[/COLOR] [COLOR=Royalblue]Not[/COLOR] Intersect(Target, Range([COLOR=brown]"D8"[/COLOR])) [COLOR=Royalblue]Is[/COLOR] [COLOR=Royalblue]Nothing[/COLOR] [COLOR=Royalblue]Then[/COLOR]

[COLOR=Royalblue]If[/COLOR] Target.Cells.CountLarge > [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]Then[/COLOR] [COLOR=Royalblue]Exit[/COLOR] [COLOR=Royalblue]Sub[/COLOR]

    Range([COLOR=brown]"26:29:,52:88"[/COLOR]).EntireRow.Hidden = Target.Value <> [COLOR=brown]"Company"[/COLOR]
 
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]


[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

 

[tlc53]

Thanks. Although now it's showing the error on line;

Range("26:29:,52:88").EntireRow.Hidden = Target.Value <> "Company"

 

[Akuini]

Sorry, replace with this:

Range("26:29, 52:88").EntireRow.Hidden = Target.Value <> "Company"

 

[tlc53]

I should have noticed that myself
Thank you! Works a charm now.

 

[Akuini]

You're welcome, glad to help, & thanks for the feedback.