Macro-enabled: hide rows on sheet B based on cell values on sheet A.

[csifood]

Sheet A - love
Sheet B - free
The code works when only sheet "love" is protected. It does not work if I protect both sheets , I get a Run-time Error Code "1004" Unable to set the Hidden Property of the range class. I need to protect both sheets.
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Activate
If Not Application.Intersect(Range("A1"), Range(Target.Address)) Is Nothing Then
Me.Unprotect "blue"
Select Case Target.Value

Case Is = "yes": Sheets("free").Rows("3:5").EntireRow.Hidden = False
Sheets("free").Rows("7:8").EntireRow.Hidden = True
Case Is = "no": Sheets("free").Rows("7:8").EntireRow.Hidden = False
Sheets("free").Rows("3:5").EntireRow.Hidden = True
End Select
Me.Protect "blue"
End If
End Sub
I have successfully used "me.uprotect..." with other codes, but to hide rows on the same sheet not on a different sheet.
I new at this and learned what I know form YouTube.
Thanks

 

[DanteAmor]

Hi and welcome to MrExcel!

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Count > 1 Then Exit Sub
  If Not Intersect(Target, Range("A1")) Is Nothing Then
    With Sheets("free")
      .Unprotect "blue"
      Select Case LCase(Target.Value)
        Case "yes"
          .Rows("3:5").EntireRow.Hidden = False
          .Rows("7:8").EntireRow.Hidden = True
        Case "no"
          .Rows("7:8").EntireRow.Hidden = False
          .Rows("3:5").EntireRow.Hidden = True
      End Select
      .Protect "blue"
    End With
  End If
End Sub

Note Code Tag:
In future please use code tags when posting code.
How to Post Your VBA Code it makes your code easier to read and copy and it also maintains VBA formatting.

 

[JoeMo]

Untested, but assuming you want the worksheet change event to be used on sheet "love" and that the password for sheet "free" is "green", try this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("A1"), Target) Is Nothing Then
    Me.Unprotect "blue"
    Sheets("free").Unprotect "green"  'change password to suit
    Select Case Target.Value
        Case Is = "yes": Sheets("free").Rows("3:5").EntireRow.Hidden = False
        Sheets("free").Rows("7:8").EntireRow.Hidden = True
        Case Is = "no": Sheets("free").Rows("7:8").EntireRow.Hidden = False
        Sheets("free").Rows("3:5").EntireRow.Hidden = True
        End Select
    Me.Protect "blue"
    Sheets("free").Protect "green"
End If
End Sub