Hide/Unhide rows based on another cell value

0
votes

I am trying to hide/unhide cells based on a cell value. When the cell value of sheet("create") equals "RCDO" it will hide rows 22:35 and unhide rows 36:49 on sheet("Form"), when value does not equal "RCDO" then unhide rows 22:35 and hide rows 36:49. The code does not work automatically, meaning I would have to click on the cells of the rows for it to run, versus the codes running as soon as I change the value of the cell. 

    Private Sub HideRow1()

Application.ScreenUpdating = False

If Sheets("Create").Range("C4").Value = "RCDO" Then
        Rows("22:35").EntireRow.Hidden = True
        Rows("36:49").EntireRow.Hidden = False

     ElseIf Sheets("Create").Range("C4").Value <> "RCDO" Then
        Rows("36:49").EntireRow.Hidden = True
        Rows("22:35").EntireRow.Hidden = False

        Application.ScreenUpdating = True
    End If


End Sub
2
excelvba

2 Answers

1
votes

Write below code in sheet("create")

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = Me.Range("C4").Address Then Call HideRow1
End Sub


Private Sub HideRow1()

    Application.ScreenUpdating = False
    If UCase(Me.Range("C4").Value) = "RCDO" Then
        Me.Rows("22:35").EntireRow.Hidden = True
        Me.Rows("36:49").EntireRow.Hidden = False
    Else
        Me.Rows("36:49").EntireRow.Hidden = True
        Me.Rows("22:35").EntireRow.Hidden = False
    End If
    Application.ScreenUpdating = True

End Sub
0
votes

put this code into a sheet called "Create" (not in module) so it works based on the event you changed the value:

 Option Compare Text
  Private Sub Worksheet_Change(ByVal Target As Range)
    'Check sheet'name and activeCell = C4
    If ActiveSheet.Name <> "Create" Or Target.Address(0, 0) <> "C4" Then Exit Sub
            ToogleHidden ([C4].Value = "RCDO")
    End Sub

    Private Sub ToogleHidden(boo As Boolean)
    Application.ScreenUpdating = False
        Worksheets("Form").Rows("22:35").EntireRow.Hidden = boo
        Worksheets("Form").Rows("36:49").EntireRow.Hidden = Not (boo)
     Application.ScreenUpdating = True
    End Sub