Lock cell based on value of another cell

1

Hi, I want cell C to F of each row to lock if cell B value = "Completed"

I currently got it to work for 1 row only but I want this to repeat for every row.

The code I have so far

Thank you in advance everyone!


Private Sub Worksheet_Change(ByVal Target As Range)
'Lock Cell after verified
If [B1] = "Completed" Then
ActiveSheet.Unprotect ("PASSWORD")
[C1:F1].Locked = True
ActiveSheet.Protect ("PASSWORD")
'Remove locked property if B3's value is anything else or is deleted.
Else
ActiveSheet.Unprotect ("PASSWORD")
[C1:F1].Locked = False
'Optional, reprotect sheet
ActiveSheet.Protect ("PASSWORD")
End If
End Sub
Post Edited
CODE Tags: You must add [CODE][/CODE] tags around your code! (click the CODE button to do this when creating a post)
Answer
Discuss

Discussion

Please edit your question and put CODE tags around your code so it will be formatted like code (it makes it a lot easier to read).
cappymer1 (rep: 120) Mar 26, '17 at 3:45 am
Add to Discussion

Answers

1
Selected Answer

I haven't been able to test this but it should do what you want.

Private Sub Worksheet_Change(ByVal Target As Range)
 
'Lock Cell after verified
If Target.Column = 2 Then

    If Target.Value = "Completed" Then
    
        ActiveSheet.Unprotect ("PASSWORD")
        Range(Cells(Target.Row, Target.Column + 1), Cells(Target.Row, Target.Column + 4)).Locked = True
        ActiveSheet.Protect ("PASSWORD")
        
    'Remove locked property if B3's value is anything else or is deleted.
    Else
    
        ActiveSheet.Unprotect ("PASSWORD")
        Range(Cells(Target.Row, Target.Column + 1), Cells(Target.Row, Target.Column + 4)).Locked = False
        'Optional, reprotect sheet
        ActiveSheet.Protect ("PASSWORD")
    
    End If
    
End If

End Sub

You just need to use the Target variable instead of hard-coding the cell references.

Discuss

Discussion

Hi Cappymer1, thanks so much. I have very little knowledge about vba, hope you don't mind I'm asking this question. Do I need to replace anything in (Target.Row?, Target.Column)? As when I placed the code in the box it show text in red.

Thanks again
MayDay1988 (rep: 4) Mar 26, '17 at 3:54 am
You need to insert it into a worksheet and NOT a Module. But, it should copy/paste from here just fine.
cappymer1 (rep: 120) Mar 26, '17 at 4:05 am
This work perfectly. Thank you!
MayDay1988 (rep: 4) Mar 26, '17 at 4:57 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login