VBA code to unlock a cell if another cell's value is greater than 6.00

[cor_dog2]

Hello everyone,

I have a protected worksheet with most cells locked and some that are unlocked. I also know the password to unlock the sheet.
Does anyone know the VBA code to monitor a cell(B29 in my case) and if it has a value of 6.00 or more than it will unlock cell B34?

Seems simple enough, but can't quite get it to work. I am a noob, so maybe that is why.

Any help would be greatly appreciated!!
Thanks!

 

[VoG]

What changes B29 - you or does it contain a formula?

 

[cor_dog2]

Hi VoG, it uses a formula to add other cells.
To be exact the formula is: =B12+B15+B18+B21+B24+B27

 

[VoG]

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Calculate()
If Range("B29").Value >= 6 Then
    Me.Unprotect Password:="pw"
    Range("B34").Locked = False
    Me.Protect Password:="pw"
End If
End Sub

Change pw to the actual password.

 

[cor_dog2]

That worked! You are awesome!!
Is it possible to have to re-protect itself if the value falls back under/less than 6? I noticed when the value fell, it remained un-protected. If it could re-protect if the value falls below 6 that would be PERFECT!!!

 

[VoG]

Try

Private Sub Worksheet_Calculate()
If Range("B29").Value >= 6 Then
    Me.Unprotect Password:="pw"
    Range("B34").Locked = False
    Me.Protect Password:="pw"
Else
    Me.Unprotect Password:="pw"
    Range("B34").Locked = True
    Me.Protect Password:="pw"
End If
End Sub

 

[cor_dog2]

Genius!! You just made my day! Thank you very much!!!
If I wanted to repeat that same setup on the same worksheet to C29 and C34 would I just create another Private Sub and repeat it that way. Or is there an easier way to repeat that?
I was thinking of:
Private Sub Worksheet_Calculate2()
If Range("C29").Value >= 6 Then
Me.Unprotect Password:="pw"
Range("C34").Locked = False
Me.Protect Password:="pw"
Else
Me.Unprotect Password:="pw"
Range("C34").Locked = True
Me.Protect Password:="pw"
End If
End Sub

 

[cor_dog2]

Actually, nevermind....I think I figured it out.
Thanks for all your help. Much appreciated VoG!!