vbbeginner
New Member
- Joined
- Apr 26, 2021
- Messages
- 3
- Office Version
- 365
- Platform
- Windows
Hi, I am trying to use worksheet_change to lock a row of cells when the value in one of the cells is "YES". The cell you enter "YES" in is J4, and I want cells A4:J4 to lock upon entering "Yes" into J4. I want this to happen for every row from row 4 to row 500. I tried adapting code I found on this forum, so below is where I've got, but it doesn't seem to work and I keep randomly getting the message that my spreadsheet is locked. Any help would be much appreciated! I know it's a simple thing to be able to do, but I've got into a mess. Many thanks.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim UserRow As Long
UserRow = Target.Row
If UserRow >= 4 And UserRow <= 500 Then
ActiveSheet.Unprotect "password"
If UCase(Range("J" & UserRow).Value) = "" Then
Range("A" & UserRow & ":J" & UserRow).Locked = False
ElseIf UCase(Range("J" & UserRow).Value) = "YES" Then
Range("A" & UserRow & ":J" & UserRow).Locked = True
End If
ActiveSheet.Protect "password"
End If
End Sub