VBA Lock Cells in Range

[tabbytomo]

Hi everyone! I've hit a bit of a brick wall with my knowledge and could do with a hand. I've got a simple sheet, I need cells A2 - D2 filling in, then once there is content in that range, lock that range AND unlock the row below (A3 - D3).

So far, I've managed to get it to lock the entire sheet when there is content in the range, and this applies no matter how many rows you complete, so for example I could get all the way down to A20 - D20 and this code would work. I'm looking for some help with that final bit, keep the row below unlocked as that will be the next set of cells we want data to go into.

Any ideas?

Current code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Const PASSWORD = "PASSWORD"
    Dim Row As Long
    If Target.Column = 4 Then
        Target.Parent.Unprotect PASSWORD
        Row = Target.Row
        Target.Parent.Range("A" & Row & ":" & "D" & Row).Locked = True
        Target.Parent.Protect PASSWORD
    End If
End Sub

 

[steve the fish]

You could use something like:

With Me
    .Unprotect PASSWORD
    .Range(.Cells(Target.Row, 1), .Cells(Target.Row, 4)).Locked = True
    .Range(.Cells(Target.Row + 1, 1), .Cells(Target.Row + 1, 4)).Locked = False
    .Protect PASSWORD
End With

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Column = 4 Then
      Me.Unprotect "Password"
      With Target.Offset(, -3).Resize(, 4)
         .Locked = True
         .Offset(1).Locked = False
      End With
      Me.Protect "Password"
   End If
End Sub

 

[tabbytomo]

Solved, cheers Fluff!

 

[Fluff]

Glad we could help & thanks for the feedback.