Using VBA to lock / unlock rows - error

[Bandito1]

Hello all,

In attached file i use the following vba code to lock / unlock rows;

When data is entered in column "J" the row locks. When i remove the data in column J the row unlocks.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Columns("A:I")) Is Nothing And Target.CountLarge > 0 Then
    If Len(Cells(Target.Row, "J").Value) Then
      MsgBox "When you closed the row with your initials you cannot change values in columns A : I." & _
             vbLf & vbLf & "Remove your initials if you are sure you want to change data.", vbExclamation
             
      Application.EnableEvents = False
      Application.Undo
      Application.EnableEvents = True
    End If
  End If
End Sub

It works perfect except for 1 thing;

When i remove the sign to unlock the row i'm unable to delete the row.
See example;

Row 3 is not locked anymore but i can't delete the row. The code fires and the data returns.

Someone knows what's wrong?

[Logit]

.
Here is my take on it ....

It appears as long as the row (in your example) that contains the term "locked" in Col J doesn't move up to replace the row immediately above it ... the macro works as you
intend.

Example : remove the term "locked" from row 2. Now delete row 2 only. The macro works as desired.

Now, attempt to delete the new row 2. You can't ... because the term "locked" is moving into what used to be row 2 / col J that was empty. You are changing the condition of Col J in row 2 ... so the macro
activates. Hope that is understandable.

Although it isn't how you want it .... you'll need to remove any data from Col J that is directly below the row you are wanting to delete.

I would presume you could change your macro to include an IF / ELSE statement that if the OFFSET of Col J directly below the row/cell J you are attempting to delete is NOT EMPTY, to allow
that row to be deleted.