VBA - HIDE/UNHIDE Rows Based on Output of a Formulated Cell

[HazelOC]

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

[SamT]

Since the calculate Event sub has no "Target" Parameter, the use of "Target" in the code is meaningless.

Change both

If Target.Column = 5 And Target.Row = 17 And Target.Value =

to

If Range("E17").Value =

And Change the others to the similar. Range("E26")

[p45cal]

Private Sub Worksheet_Calculate()
If Cells(17, 5).Value = "Non Significant Change - Minor Local Governance applies" Then
  Range("1:18,29:29,31:121,129:129").EntireRow.Hidden = False
  Range("19:28,30:30,122:128,130:301").EntireRow.Hidden = True

ElseIf Cells(17, 5).Value = "Significant Change - complete the additional materiality questions below" Then
  Range("1:17,19:26").EntireRow.Hidden = False
  Range("18:18,27:301").EntireRow.Hidden = True

ElseIf Cells(26, 5).Value = "Significant Non Material [Standard]" Then
  Range("1:17,19:27,30:82,99:121,129:300").EntireRow.Hidden = False
  Range("18:18,28:29,83:98,122:128,301:301").EntireRow.Hidden = True

ElseIf Cells(26, 5).Value = "Significant Change - Material" Then
  Range("1:17,19:26,28:28,30:82,99:121,129:129,301:301").EntireRow.Hidden = False
  Range("18:18,27:27,29:29,83:98,122:128,300:300").EntireRow.Hidden = True
End If
End Sub

[HazelOC]

Thank you so much for your help!!!
I am having another issue when I input the new code (have amended some of the range rows stated above)
Runtime error 2147417848 (80010108) Method Hidden of Object Range failed.
When I remove Macro I get a Runtime error 1004 - Unable to set the Hidden property of Range class.
I have attached the document form as probably easier for you to look at - My formulas are stored under hidden columns K-S.
Really appreciate your help with this.

Private Sub Worksheet_Calculate() 
    If Cells(17, 5).Value = "Non Significant Change - Minor Local Governance applies" Then 
        Range("1:18,29:29,31:123,131:163").EntireRow.Hidden = False 
        Range("19:28,30:30,124:130,164:313").EntireRow.Hidden = True 
        
    ElseIf Cells(17, 5).Value = "Significant Change - complete the additional materiality questions below" Then 
        Range("1:17,19:26").EntireRow.Hidden = False 
        Range("18:18,27:313").EntireRow.Hidden = True 
        
    ElseIf Cells(26, 5).Value = "Significant Non Material [Standard]" Then 
        Range("1:17,19:27,30:123,131:312").EntireRow.Hidden = False 
        Range("18:18,28:29,124:130,313:313").EntireRow.Hidden = True 
        
    ElseIf Cells(26, 5).Value = "Significant Change - Material" Then 
        Range("1:17,19:26,28:28,30:123,131:311,313:313").EntireRow.Hidden = False 
        Range("18:18,27:27,29:29,124:130,312:312").EntireRow.Hidden = True 
    End If 
End Sub

[p45cal]

The business of hiding/showing rows itself sparks off another Worksheet_Calculate event (I haven't looked in depth as to why), and so the routine calls itself again and again until it runs out of resources.
Temporarily disabling Events will stop that:

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
    If Cells(17, 5).Value = "Non Significant Change - Minor Local Governance applies" Then
        Range("1:18,29:29,31:123,131:163").EntireRow.Hidden = False
        Range("19:28,30:30,124:130,164:313").EntireRow.Hidden = True
         
    ElseIf Cells(17, 5).Value = "Significant Change - complete the additional materiality questions below" Then
        Range("1:17,19:26").EntireRow.Hidden = False
        Range("18:18,27:313").EntireRow.Hidden = True
         
    ElseIf Cells(26, 5).Value = "Significant Non Material [Standard]" Then
        Range("1:17,19:27,30:123,131:312").EntireRow.Hidden = False
        Range("18:18,28:29,124:130,313:313").EntireRow.Hidden = True
         
    ElseIf Cells(26, 5).Value = "Significant Change - Material" Then
        Range("1:17,19:26,28:28,30:123,131:311,313:313").EntireRow.Hidden = False
        Range("18:18,27:27,29:29,124:130,312:312").EntireRow.Hidden = True
    End If
Application.EnableEvents = True
End Sub

While you're developing this, if there's an error in the code it should stop on the offending line to alert you as to where the error occurred, however, if you stop the code running at this point, that final line:
Application.EnableEvents = True
may not be executed, and that would stop other events firing (and this one too), so be ready in the Immediate pane with that line to apply it manually.

[HazelOC]

Thank you so much for helping with this, I can get this working now, have been scratching my head the past few days.
Really appreciate it.