Excel time/date stamp of a cell change

[SemiAuto40]

Hey All, This must be one of the most common questions in Excel but all of the "solutions" that I have found while searching do not work (or I am not implementing properly).

This is the problem: once a value changes in a cell on Sheet1, I need the corresponding cell in Sheet2 to get the Now() value when the cell on Sheet1 was changed. Ex: Sheet1!E6 gets a value - then Sheet2!E6 shows something like 2/17/12 8:45:33.

I want this to be generic enough to apply to a range of cells, not just a one time event.... and such that all of the cells in Sheet2 holding the Now() value don't all display the exact same time like my failed attempts do.

If VB is involved I need specific instructions on where to put in and please phrase it in a 4th grade level.

Please My sanity is at stake here.

 

[Joe4]

Welcome to the Board!

Yes, this definitely requires VBA, specifically, an Event Procedure that is automatically fired when a cell is updated.

Go to Sheet1 and right click on the sheet tab name at the bottom of your screen. Select the View Code option, and paste this code into your VB window.

Private Sub Worksheet_Change(ByVal Target As Range)
 
    Dim myAddress As String
 
'   Only run when a single cell is updated
    If Target.Count = 1 Then
'   Capture the address of the cell being updated
        myAddress = Target.Address
'   Add the date/time stamp to the corresonding cell on sheet 2
        Sheets("Sheet2").Range(myAddress) = Now()
    End If
 
End Sub

Now, as long as Macros are enabled in your workbook, whenever a single cell is updated on Sheet 1, the corresponding cell on Sheet 2 will record the date/time stamp.

 

[SemiAuto40]

JOE4... first of all... in one word... WOW! It is not often that I get wowed!

For the hour++ that I spent trying to find the solution to this one Excel question/problem and the additional time spent trying to implement each one of the "solutions" that were offered only to find none did exactly what I needed --- Your exact solution worked perfectly AND was the most elegant in it's simplicity and was comprised of only the minimal amount of commented code to do the job! Again... WOW! I was worried when I saw the enormous number of postings to this board and just knew mine would be so far down the current latest postings that I would surely not get an answer because it rapidly got pushed down the cue by the volume of questions. I can forget those other boards that I had to register for in order to look at the proposed answer that I was looking for only to find out how little useful that I got as a result of it. My wasted time with other boards will have to be the price I paid yesterday... but now I've found THE board on Excel topics and Mr. Excel is the perfect name for this board!
Thanks to Google for pointing me to you.

 

[Joe4]

Glad we were able to help out!

The topics on this board do move pretty quickly, but many of us scan all unanswered posts from the past 24 hours to find questions that might have slipped through the cracks. Unfortunately, we are not always able to answer all the questions, but we sure answer a lot!

 

[litsa]

hello, is it possible to time/date stamp when a cell change to a specific value?(Completed)
thanks!

 

[Joe4]

Welcome to the Board!

Sure, just add the condition to the IF statement, i.e.
change:

If Target.Count = 1 Then

to:

If (Target.Count = 1) and (Target = "Completed") Then

 

[litsa]

Thank you!!!!-)

 

[SmitV]

Hello,

I would like to add time/date stamp on sheet 1, cell E7 only when any of the cells are modified. Can this be done?

 

[Joe4]

I would like to add time/date stamp on sheet 1, cell E7 only when any of the cells are modified. Can this be done?

That's pretty easy. We can basically remove all the criteria, as we don't need to check which cells are being updated.
We just need to add code to disable events while the code is running, so the change we are making in E7 doesn't call the code itself, and we cut stuck in an infinite loop.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Application.EnableEvents = False
    Range("E7") = Now()
    Application.EnableEvents = True
    
End Sub

 

[SmitV]

Excellent!! I am really happy that I signed up for this forum.