worksheet_change not working properly

[vbbeginner]

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.

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

 

[RoryA]

Welcome to the forum.

It would be better to monitor column J specifically. For example:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not intersect(Target, Range("J4:J100")) Is nothing then
Me.Unprotect "password"
dim cell as range
for each cell in intersect(Target, Range("J4:J100")).Cells
Range("A" & cell.Row & ":J" & cell.Row).Locked = (UCase(cell.Value) = "YES")
next cell
Me.Protect "password"
End If
End Sub

 

[jkpieterse]

Before running that code, first unlock all relevant cells. If the user needs to be able to clear the yes-es, change the :J to :I in your code.

 

[vbbeginner]

Private Sub Worksheet_Change(ByVal Target As Range) If Not intersect(Target, Range("J4:J100")) Is nothing then Me.Unprotect "password" dim cell as range for each cell in intersect(Target, Range("J4:J100")).Cells Range("A" & cell.Row & ":J" & cell.Row).Locked = (UCase(cell.Value) = "YES") next cell Me.Protect "password" End If End Sub

Hi,
Thank you for replying so promptly. I tried the code but I am still getting a weird error where if I try and input values into some of the cells when the cell in column J is blank, as opposed to "YES", I get the message that the cell I'm trying to change is on a protected sheet. There doesn't seem to be any logic to it - some cells I can input values into, and some I get the 'protected sheet' message. So for example, when J7 is blank, if I try and type into A7, I get the error message, but when J8 is blank I can type into A8.

 

[jkpieterse]

Read my first reply again...

 

[vbbeginner]

Hi,
Apologies, I didn't see your reply. I thought I had unlocked the cells before running the code, but I just unlocked them again and I don't seem to be getting those errors now, so that plus the new code seems to have worked. Many thanks for your help.