Creating timestamp and locking cells

[briguy89]

Hello all,

I am looking to trigger an event in column 1, which then creates a timestamp in column 2 and locks both column 1 and 2. Columns 3-7 will contain editable data, then column 8 will be for Status updates. When the status drop down box is selected to be "complete" I would like the entire row, cut and pasted to the next sheet. Any help is much appreciated.

If Target.column = 1 Then
Application.EnableEvents = False
confirm = MsgBox("Are you ready to start another entry?", vbYesNo, "Cell Lock Confirmation")
Select Case confirm
Case Is = vbYes
Dim cell As Range
With ActiveSheet
.Unprotect Password:="test"
.Cells.Locked = False
For Each cell In ActiveSheet.UsedRange
If cell.Value = "" Then
cell.Locked = False
Else
cell.Locked = True
Target.Offset(0, 1) = Format(Now(), "m/d/yyyy - h:mm:ss AM/PM")
End If
Next cell
.Protect Password:="test"
End With
Case Is = vbNo
Application.Undo
End Select
End If
Application.EnableEvents = True


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.column = 8 Then
If Target = "Complete" Then
Application.EnableEvents = False
nxtRow = Sheets("Completed").Range("H" & Rows.Count).End(xlUp).Row + 1
Target.EntireRow.Copy _
Destination:=Sheets("Completed").Range("A" & nxtRow)
Target.EntireRow.Delete
End If
End If
Application.EnableEvents = True
End Sub