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.