Date function to record changes within a row

[Guest]

Hello,
Is there any worksheet function available that will record the date when a
change is made to a specific row? For example, I have a worksheet tracking
several projects with cost, project manager, etc. Anytime there is a change
to one of those items, I would like the "Date Last Edited" column to update
in that row only. Can this be done?
Thanks.

 

[Don Guillett]

Right click sheet tab>view code>insert this>modify to suit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row <> 14 Then Exit Sub
Application.EnableEvents = False
Cells(Target.Row, "a") = Time '"Changed"
Application.EnableEvents = True
End Sub

 

[Gord Dibben]

There is no reliable worksheet function to enter a static date other than to hit
CTRL + ;(semi-colon) for date or CTRL + SHIFT + ; to enter static time.

Here is code that will update Column F any time a change is made to cells in
columns A:E

Adjust to accommodate "etc."

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("A:E"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
Cells(.Row, "F").ClearContents
Else
With .Offset(0, 1)
Cells(.Row, "F").Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste the above into that sheet module.


Gord Dibben MS Excel MVP