Hi,

I'm using the following code for an automated date stamp. Whenever data is found cells (AI37:IV37), the two rows above automatically insert the day number (dd) and month (mmm), and whenever data is deleted from that range, the day and month are deleted.

I'd like to protect the (ai37:iv37) cells in which the day and month are stamped, however, and I don't know how to change the code. They should become protected after the date stamp is made if the cell below in the ai37:iv37 range was = "Attendance". If that cell then changed to a blank or anything else, they should be unprotected to allow the user to edit the date.

I hope this is not too much trouble. Thanks.

Code:


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("AI37:IV37"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(-1, 0).ClearContents
.Offset(-2, 0).ClearContents
Else
With .Offset(-1, 0)
.NumberFormat = "dd"
.Value = Date
End With
With .Offset(-2, 0)
.NumberFormat = "mmm"
.Value = Date
End With
End If
Application.EnableEvents = True
End If
End With
End Sub

Hi,

Just after .Value = Date
add .Locked = True

and do not forget at the end of your code
to add
ActiveSheet.Protect

HTH
Cheers
Carim