VBA Code HELP!!!

[BigCountry37]

Trying to write a code that will accomplish the following:
1. If cell "H16" is "Yes" then unlock range E47:E53
2. If cell "H16" is "No" then fill range E47:E53 with zero's and lock.

Given-working on Sheet5, and let me know if the range E47:E53 needs to be normally locked or unlocked. Also sheet will be protected with a password.

Please help!!

 

[VoG]

Try this - the cells should be unlocked to begin with:

Sub LockRange()
With Sheets("Sheet5")
    .Unprotect Password:="abc"
    Select Case .Range("H16").Value
        Case "No": .Range("E47:E53").Locked = False
        Case "Yes": .Range("E47:E53").Value = 0: .Range("E47:E53").Locked = True
    End Select
    .Protect Password:="abc"
End With
End Sub

 

[BigCountry37]

Doesn't seem to be working...

 

[BigCountry37]

started out with this...it's close, but not filling in 0's and it's locking out the E47:E53 range in both cases...."Yes" and "No" in H16.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
' Locks range if condition is met
If (Intersect(Target, Range("$H16")) Is Nothing) Then Exit Sub

If Range("H16").Value = "Yes" Then
ActiveSheet.Unprotect Password:="password"
Range("E47:E53").Select
Selection.Locked = False
ActiveSheet.Protect Password:="password"

Else
ActiveSheet.Unprotect Password:="password"
Range("E47:E53").Select
Selection.Locked = True
ActiveSheet.Protect Password:="password"

End If


End Sub

 

[VoG]

I just tested it and it works for me. You need to enter a Yes or No in H16 then run the macro.

 

[BigCountry37]

will try it again.

 

[VoG]

This works for me.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) <> "H16" Then Exit Sub
With Me
    .Unprotect Password:="abc"
    Select Case .Range("H16").Value
        Case "No": .Range("E47:E53").Locked = False
        Case "Yes": .Range("E47:E53").Value = 0: .Range("E47:E53").Locked = True
    End Select
    .Protect Password:="abc"
End With
End Sub

 

[BigCountry37]

Brilliant!! Got it working. It didn't like my all uppercase YES and NO
Thanks for the help!!!

 

[VoG]

Glad it is working. You can make it case insensitive like this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) <> "H16" Then Exit Sub
With Me
    .Unprotect Password:="abc"
    Select Case LCase(.Range("H16").Value)
        Case "no": .Range("E47:E53").Locked = False
        Case "yes": .Range("E47:E53").Value = 0: .Range("E47:E53").Locked = True
    End Select
    .Protect Password:="abc"
End With
End Sub