How to hide rows on another sheet with VBA?

[Luke1997]

I'm a complete newbie at this and only found info from searching through google so any help would be appreciated. I currently have a VBA set up on "Sheet1" where if cell B5 = "No" it hides rows 6:7, if it = "Yes" it unhides them. Currently that works fine. What i would like it to do is also hide rows 27:37 on "Sheet 2" if (Sheet 1 B5) = "No". Below is currently what I have.

Public Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("B5"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "No": Rows("6:7").EntireRow.Hidden = True
Case Is = "Yes": Rows("6:7").EntireRow.Hidden = False


End Select
End If
End Sub

 

[dmt32]

Hi
welcome to forum

See if this update to your code does what you want

Place in your Sheet1 code page

Public Sub Worksheet_Change(ByVal Target As Range)
    Dim HideRows As Boolean
    If Not Application.Intersect(Range("B5"), Target) Is Nothing Then
     HideRows = UCase(Target.Value) = "NO"
     Rows("6:7").EntireRow.Hidden = HideRows
     Worksheets("Sheet2").Rows("27:37").EntireRow.Hidden = HideRows
    End If
End Sub

solution assumes value in B5 is changed by something other than formula

Hope Helpful

Dave

 

[Luke1997]

Hi
welcome to forum

See if this update to your code does what you want

Place in your Sheet1 code page

Public Sub Worksheet_Change(ByVal Target As Range)
    Dim HideRows As Boolean
    If Not Application.Intersect(Range("B5"), Target) Is Nothing Then
     HideRows = UCase(Target.Value) = "NO"
     Rows("6:7").EntireRow.Hidden = HideRows
     Worksheets("Sheet2").Rows("27:37").EntireRow.Hidden = HideRows
    End If
End Sub

solution assumes value in B5 is changed by something other than formula

Hope Helpful

Dave

Hi Dave,

That worked perfectly, thank you for your help

 

[dmt32]

That worked perfectly, thank you for your help

Most welcome & appreciate your feedback

Dave