Macro to hide/unhide rows based on formula result

[amarch00]

Hi, I'm new to this forum so apologize in advance if I'm screwing up any protocols.

I have a spreadsheet whereby I'm trying to hide/unhide rows based on a formula result. The formula is an If statement that looks at a number of fields and provides either a "YES" or "NO" result.

The macro was working but then the more I added to it I started to receive a run-time error '-2147417848 (Method 'Hidden' of object 'Range' failed) and then excel locks up.

The code is as follows:
Private Sub Worksheet_Calculate()


'Alabama State
If Range("M12") = "YES" Then
Rows("17:18").Hidden = False
ElseIf Range("M12") = "NO" Then
Rows("17:18").Hidden = True
End If

'Arizona State
If Range("M26") = "YES" Then
Rows("31:33").Hidden = False
ElseIf Range("M26") = "NO" Then
Rows("31:33").Hidden = True
End If

'Arizona Local
If Range("N26") = "YES" Then
Rows("34:35").Hidden = False
ElseIf Range("N26") = "NO" Then
Rows("34:35").Hidden = True
End If

'Arkansas State
If Range("M37") = "YES" Then
Rows("42:44").Hidden = False
ElseIf Range("M37") = "NO" Then
Rows("42:44").Hidden = True
End If

'Arkansas Local
If Range("N37") = "YES" Then
Rows("45:46").Hidden = False
ElseIf Range("N37") = "NO" Then
Rows("45:46").Hidden = True
End If

'Colorado State
If Range("M128") = "YES" Then
Rows("133:136").Hidden = False
ElseIf Range("M128") = "NO" Then
Rows("133:136").Hidden = True
End If

'Connecticut State
If Range("M138") = "YES" Then
Rows("143:146").Hidden = False
ElseIf Range("M138") = "NO" Then
Rows("143:146").Hidden = True
End If

'Delaware State
If Range("M148") = "YES" Then
Rows("153:154").Hidden = False
ElseIf Range("M148") = "NO" Then
Rows("153:154").Hidden = True
End If

'Georgia State
If Range("M185") = "YES" Then
Rows("190:195").Hidden = False
ElseIf Range("M185") = "NO" Then
Rows("190:195").Hidden = True
End If

'Idaho State
If Range("M203") = "YES" Then
Rows("208:209").Hidden = False
ElseIf Range("M203") = "NO" Then
Rows("208:209").Hidden = True
End If

'Indiana State
If Range("M230") = "YES" Then
Rows("235:236").Hidden = False
ElseIf Range("M230") = "NO" Then
Rows("235:236").Hidden = True
End If

'Kansas State
If Range("M244") = "YES" Then
Rows("249:250").Hidden = False
ElseIf Range("M244") = "NO" Then
Rows("249:250").Hidden = True
End If

'Massachusetts State
If Range("M332") = "YES" Then
Rows("337:341").Hidden = False
ElseIf Range("M332") = "NO" Then
Rows("337:341").Hidden = True
End If

'Massachusetts Local
If Range("N332") = "YES" Then
Rows("342:343").Hidden = False
ElseIf Range("N332") = "NO" Then
Rows("342:343").Hidden = True
End If

'Michigan State
If Range("M347") = "YES" Then
Rows("352:358").Hidden = False
ElseIf Range("M347") = "NO" Then
Rows("352:358").Hidden = True
End If

'Michigan Local
If Range("N347") = "YES" Then
Rows("359:360").Hidden = False
ElseIf Range("N347") = "NO" Then
Rows("359:360").Hidden = True
End If

'Minnesota State
If Range("M364") = "YES" Then
Rows("369:374").Hidden = False
ElseIf Range("M364") = "NO" Then
Rows("369:374").Hidden = True
End If

'Mississippi State
If Range("M376") = "YES" Then
Rows("381:383").Hidden = False
ElseIf Range("M376") = "NO" Then
Rows("381:383").Hidden = True
End If

'Mississippi Local
If Range("N376") = "YES" Then
Rows("384:385").Hidden = False
ElseIf Range("N376") = "NO" Then
Rows("384:385").Hidden = True
End If

'New Jersey State
If Range("M434") = "YES" Then
Rows("439:441").Hidden = False
ElseIf Range("M434") = "NO" Then
Rows("439:441").Hidden = True
End If

'New Jersey Local
If Range("N434") = "YES" Then
Rows("442:443").Hidden = False
ElseIf Range("N434") = "NO" Then
Rows("442:443").Hidden = True
End If

'North Carolina State
If Range("M546") = "YES" Then
Rows("551:554").Hidden = False
ElseIf Range("M546") = "NO" Then
Rows("551:554").Hidden = True
End If


End Sub

Any assistance would be very much appreciated!!

 

[VoG]

Which line of code gives the error?

 

[amarch00]

I get a yellow arrow beside line 11 (Rows("17:18").Hidden = True).