Date Stamping particular cells in spreadsheet

[kogersdad]

I have an Excel 2007 spreadsheet that I share with several other people. I would like to date stamp the last time certain columns are updated (adding new data to a empty cell or changing existing data). Is there a formula I could put in D9 (for example) that would monitor C9 for changes and when C9 changes, D9 would include a date stamp?
Thanks for future help!!
Tim

[tommyt61]

This will enter todays date in Cell D9 when a change is made to Cell C9

paste the supplied code into Private Sub Worksheet_Change(ByVal Target As Range) routine.

also set date format you prefer in cell D9 properties.


1) Open the VB editor
1) On the left pane window, double click the sheet where you need your code to run.
2) Now, at the top of the code window you will see... (General) with a drop down, and (Declarations) with a drop down.
3) Click the drop down by (General) and select Worksheet.
4) Clcik the drop down by (Declarations) and select change

it should automatically create


Private Sub Worksheet_Change(ByVal Target As Range)


End Sub

If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = "$C$9" Then
Application.EnableEvents = False
Range("D9").Value = Format(Date, "dd/mm/yy")
 

'Turn events back on
Application.EnableEvents = True
'Allow run time errors again
On Error GoTo 0
End If

[tommyt61]

If you want to monitor Column C for changes made in any cell use this code

If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Sheets("sheet1").Range("c:c")) Is Nothing Then
Application.EnableEvents = False
Range("D9").Value = Format(Date, "dd/mm/yy")
 

'Turn events back on
Application.EnableEvents = True
'Allow run time errors again
On Error GoTo 0
End If

[kogersdad]

Ok, that worked perfectly! Could I possibly ask for one more enhancement? Is there a way to monitor each cell in Column C and have it put the date of change in the corresponding row in Column D? For example, each time C10 changes, D10 is populated with a date stamp and each time C11 changes, D11 is stamped? That was my original question but I didnt word it correctly. Thank you so much for you help. I love this forum! It never fails me!!

[tommyt61]

This should fix you up.

  If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Sheets("sheet1").Range("c:c")) Is Nothing Then
Application.EnableEvents = False
Target.Offset(0, 1) = Format(Date, "dd/mm/yy")
'Turn events back on
Application.EnableEvents = True
'Allow run time errors again
On Error GoTo 0
End If

[tommyt61]

If you need a time stamp as well as date use this line of code instead of current one. I thought this might help you in case a cell gets updated more than once during a given day.

Target.Offset(0, 1) = Format(DateTime.Now, "yyyy-MM-dd hh:mm:ss")

[kogersdad]

Absolutely without a doubt PERFECT!! I will say it again. This is the best website on the internet!!
Thank you so much.
Tim