How to set current day when a cell in a colum change

[Guest]

I wold like some help on the below matter:

ID Status ChangeDate Resp.
---------------------------------------------
1 Closed 2006-06-01 XXXX
2 New 2006-06-20 YYYY
3 Working 2006-07-01 XXXX

Every time Status is change I would like to have ChangeDate to be updated
current date and Resp. with the Name/initials of the person editing the Excel
Sheet - any suggestions?

 

[Guest]

Steen,

The following code should put you in the right direction. It has to be
placed in the sheet object.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
If Target.Offset(0, -1) = "" Then
Target.Offset(0, -1) = Target.Offset(-1, -1) + 1
End If
Target.Offset(0, 1) = Now
Target.Offset(0, 2) = Environ("username")
End If
End Sub

 

[Guest]

Hi galimi

Nice - it work quite fine.

I have a quistion - what does this line take care of:

If Target.Offset(0, -1) = "" Then
Target.Offset(0, -1) = Target.Offset(-1, -1) + 1

/Stony