lock cells and run macro

[TGwilding]

I have the following macro that hides rows when a particular cell is marked yes and leaves rows unhidden when marked no:
Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Cells.Count > 1 Then Exit Sub

Application.EnableEvents = False


If Target.Address(0, 0) = "J147" And Target <> "" Then Rows("149:154").Hidden = False

If Target.Address(0, 0) = "J147" And Target = "" Then Rows("149:154").Hidden = True


If Target.Address(0, 0) = "J129" And Target <> "" Then Rows("130:139").Hidden = False

If Target.Address(0, 0) = "J129" And Target = "" Then Rows("130:139").Hidden = True


If Target.Address(0, 0) = "J62" And Target <> "" Then Rows("72:75").Hidden = False

If Target.Address(0, 0) = "J62" And Target = "" Then Rows("72:75").Hidden = True

Application.EnableEvents = True


If Target.Cells.Count > 1 Then Exit Sub


If Intersect(Target, [J:J]) Is Nothing Then Exit Sub

Application.EnableEvents = False


If Target <> "" Then Rows(Target.Row + 1 & ":" & Target.Row + 2).Hidden = False

If Target.Address(0, 0) = "J14" And Target = "" Then Rows("15:16").Hidden = True

If Target.Address(0, 0) = "J18" And Target = "" Then Rows("19:20").Hidden = True

If Target.Address(0, 0) = "J23" And Target = "" Then Rows("24:25").Hidden = True

If Target.Address(0, 0) = "J28" And Target = "" Then Rows("29:30").Hidden = True

If Target.Address(0, 0) = "J32" And Target = "" Then Rows("33:34").Hidden = True

If Target.Address(0, 0) = "J36" And Target = "" Then Rows("37:38").Hidden = True

If Target.Address(0, 0) = "J54" And Target = "" Then Rows("55:56").Hidden = True

If Target.Address(0, 0) = "J58" And Target = "" Then Rows("59:60").Hidden = True

If Target.Address(0, 0) = "J78" And Target = "" Then Rows("79:80").Hidden = True

If Target.Address(0, 0) = "J83" And Target = "" Then Rows("84:85").Hidden = True

If Target.Address(0, 0) = "J88" And Target = "" Then Rows("89:90").Hidden = True

If Target.Address(0, 0) = "J95" And Target = "" Then Rows("96:97").Hidden = True

If Target.Address(0, 0) = "J102" And Target = "" Then Rows("103:104").Hidden = True

If Target.Address(0, 0) = "J114" And Target = "" Then Rows("115:116").Hidden = True

If Target.Address(0, 0) = "J118" And Target = "" Then Rows("119:120").Hidden = True

If Target.Address(0, 0) = "J125" And Target = "" Then Rows("126:127").Hidden = True

If Target.Address(0, 0) = "J141" And Target = "" Then Rows("142:143").Hidden = True



Application.EnableEvents = True


End Sub

I would like to lock the sheet so that only certain cells can be selected to input data, however, when I lock the cells of the rows that hide/unhide, the code does not work. Does anyone know how to get around this? Any help would be appreciated.
thanks

 

[Doug.T]

Add this at the top of your macro. It will let the macro run on a protected sheet:

ActiveSheet.Protect userinterfaceonly:=True, Password:="mypassword"

......or if it is protected without a password:

ActiveSheet.Protect userinterfaceonly:=True