HazelOC
02-16-2017, 07:25 AM
Hi All,
Hoping you can help.
I have a very long spreadsheet which I want to be able to hide unhide rows/sections based on formulated answers.
I can get the code to work when I use a dropdown list however unable to get it working using a formulated cell. I am new to VBA so any help is much appreciated.
Private Sub Worksheet_Calculate()
If Target.Column = 5 And Target.Row = 17 And Target.Value = "Non Significant Change - Minor Local Governance applies" Then
Rows("1:18").EntireRow.Hidden = False
Rows("19:28").EntireRow.Hidden = True
Rows("29").EntireRow.Hidden = False
Rows("30").EntireRow.Hidden = True
Rows("31:121").EntireRow.Hidden = False
Rows("122:128").EntireRow.Hidden = True
Rows("129").EntireRow.Hidden = False
Rows("130:301").EntireRow.Hidden = True
ElseIf Target.Column = 5 And Target.Row = 17 And Target.Value = "Significant Change - complete the additional materiality questions below" Then
Rows("1:17").EntireRow.Hidden = False
Rows("18").EntireRow.Hidden = True
Rows("19:26").EntireRow.Hidden = False
Rows("27:301").EntireRow.Hidden = True
ElseIf Target.Column = 5 And Target.Row = 26 And Target.Value = "Significant Non Material [Standard]" Then
Rows("1:17").EntireRow.Hidden = False
Rows("18").EntireRow.Hidden = True
Rows("19:27").EntireRow.Hidden = False
Rows("28:29").EntireRow.Hidden = True
Rows("30:82").EntireRow.Hidden = False
Rows("83:98").EntireRow.Hidden = True
Rows("99:121").EntireRow.Hidden = False
Rows("122:128").EntireRow.Hidden = True
Rows("129:300").EntireRow.Hidden = False
Rows("301").EntireRow.Hidden = True
ElseIf Target.Column = 5 And Target.Row = 26 And Target.Value = "Significant Change - Material" Then
Rows("1:17").EntireRow.Hidden = False
Rows("18").EntireRow.Hidden = True
Rows("19:26").EntireRow.Hidden = False
Rows("27").EntireRow.Hidden = True
Rows("28").EntireRow.Hidden = False
Rows("29").EntireRow.Hidden = True
Rows("30:82").EntireRow.Hidden = False
Rows("83:98").EntireRow.Hidden = True
Rows("99:121").EntireRow.Hidden = False
Rows("122:128").EntireRow.Hidden = True
Rows("129").EntireRow.Hidden = False
Rows("300").EntireRow.Hidden = True
Rows("301").EntireRow.Hidden = False
End If
End Sub
Hoping you can help.
I have a very long spreadsheet which I want to be able to hide unhide rows/sections based on formulated answers.
I can get the code to work when I use a dropdown list however unable to get it working using a formulated cell. I am new to VBA so any help is much appreciated.
Private Sub Worksheet_Calculate()
If Target.Column = 5 And Target.Row = 17 And Target.Value = "Non Significant Change - Minor Local Governance applies" Then
Rows("1:18").EntireRow.Hidden = False
Rows("19:28").EntireRow.Hidden = True
Rows("29").EntireRow.Hidden = False
Rows("30").EntireRow.Hidden = True
Rows("31:121").EntireRow.Hidden = False
Rows("122:128").EntireRow.Hidden = True
Rows("129").EntireRow.Hidden = False
Rows("130:301").EntireRow.Hidden = True
ElseIf Target.Column = 5 And Target.Row = 17 And Target.Value = "Significant Change - complete the additional materiality questions below" Then
Rows("1:17").EntireRow.Hidden = False
Rows("18").EntireRow.Hidden = True
Rows("19:26").EntireRow.Hidden = False
Rows("27:301").EntireRow.Hidden = True
ElseIf Target.Column = 5 And Target.Row = 26 And Target.Value = "Significant Non Material [Standard]" Then
Rows("1:17").EntireRow.Hidden = False
Rows("18").EntireRow.Hidden = True
Rows("19:27").EntireRow.Hidden = False
Rows("28:29").EntireRow.Hidden = True
Rows("30:82").EntireRow.Hidden = False
Rows("83:98").EntireRow.Hidden = True
Rows("99:121").EntireRow.Hidden = False
Rows("122:128").EntireRow.Hidden = True
Rows("129:300").EntireRow.Hidden = False
Rows("301").EntireRow.Hidden = True
ElseIf Target.Column = 5 And Target.Row = 26 And Target.Value = "Significant Change - Material" Then
Rows("1:17").EntireRow.Hidden = False
Rows("18").EntireRow.Hidden = True
Rows("19:26").EntireRow.Hidden = False
Rows("27").EntireRow.Hidden = True
Rows("28").EntireRow.Hidden = False
Rows("29").EntireRow.Hidden = True
Rows("30:82").EntireRow.Hidden = False
Rows("83:98").EntireRow.Hidden = True
Rows("99:121").EntireRow.Hidden = False
Rows("122:128").EntireRow.Hidden = True
Rows("129").EntireRow.Hidden = False
Rows("300").EntireRow.Hidden = True
Rows("301").EntireRow.Hidden = False
End If
End Sub