Excel: Locking cells based on value in another cell

[JCGSTL]

I have a spreadsheet that is protected, leaving only specific cells unlocked for users to edit.

I want to lock a range within the sheet if the value of B7 is greater than 34.

Based upon thread68-1318817 I tried the following routine:

Private Sub Small_Group_supplement()
If [B7] > 34 Then
Range("b42:i42").Locked = True
Else
Range("b42:i42").Locked = False

End If
End Sub

When I protect the sheet again nothing appears to happen. I assume I am missing something. Can anyone enlighten me?

Many thanks in advance!

 

[GlennUK]

You should post VBA queries in this forum : forum707

In the meantime, how are you running this macro? It sounds like you'd be better off putting something similar in the worksheet calculate event module.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[JCGSTL]

Glen
Thanks for the suggestion, I'll repost the thread as you suggest.
Regards
John