How to lock cells based on another

[cdulong]

Hello,

I am looking for a method (probably a macro of some sort) that will lock a range of cells (c24:f24) if another cell(B30) has a value of ""(empty), I have looked around and found some code to perform this but I can't figure out exactly were to put it and how to properly make it run.

Just as some extra information the sheet that this is currently needed in is protected with a password. So I would like to work this into the logic if possible to ensure the sheet is protect with such password after the lock or unlock has been performed.

Below you will find the code that located but not sure were to put it and how to modify it to my needs.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
' Locks range if condition is met
If (Intersect(Target, Range("$A1")) Is Nothing) Or _
(Target.Cells.Count > 1) Then Exit Sub

If Range("A1").Value = "1" Then
ActiveSheet.Unprotect
Range("B1").Select
Selection.Locked = True
ActiveSheet.Protect

Else
ActiveSheet.Unprotect
Range("B1").Select
Selection.Locked = False
ActiveSheet.Protect

End If

End Sub

Thanks,
cdulong

 

[SkipVought]

Hi,

Right-click the Sheet Tab and select View Code. Paste in the code window.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    ' Locks range if condition is met
    If (Intersect(Target, Range("B30")) Is Nothing) Or _
        (Target.Cells.Count > 1) Then Exit Sub
   
    ActiveSheet.Unprotect
    
    If Range("B30").Value = "" Then
        Range("c24:f24").Locked = True
 
    Else
        Range("c24:f24").Locked = False
    
    End If
    
    ActiveSheet.Protect
           
End Sub

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}