|
How to set current day when a cell in a colum change
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? |
How to set current day when a cell in a colum change
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 -- http://HelpExcel.com 516-984-0252 "Steen" wrote: 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? |
How to set current day when a cell in a colum change
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 "galimi" wrote: 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 -- http://HelpExcel.com 516-984-0252 "Steen" wrote: 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? |
| All times are GMT +1. The time now is 05:21 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com