Capturing date and time stamp on when data is entered in a cell

[seethanaboina]

Hi Hiker, I am new to using macros and I am trying to capture the date and time stamp of when data is entered into some cells. I was able to use an example found in one of the threads successfully. However, it worked on one row (say column A) and identified the date/time stamp as required in a different column (say I). How do I replicate this for another column (say B) to identify the date/time stamp in another column (J)? Please help me? The code I used is

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
If Target = "" Then
Target.Offset(, 8) = ""
Exit Sub
End If
With Application
.EnableEvents = False
.ScreenUpdating = False
Target.Offset(, 8) = Format(Date, "mmm dd yyyy")
.EnableEvents = True
.ScreenUpdating = True
End With

 

[SteveO59L]

Change

If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub

to

If Intersect(Target, Range("b:b")) Is Nothing Then Exit Sub

 

[Joe4]

Simply change this row:

If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub

to

If Intersect(Target, Range("A:[B][COLOR=red]B[/COLOR][/B]")) Is Nothing Then Exit Sub

 

[seethanaboina]

Hi Joe4, Changing (A:A) to (A:B) worked. I am now able to get the date/time stamp for any changes in Columns A and B in columns I and J. Thank you. How do I go about capturing the date and time stamps if the cells are not consecutive (meaning, the cells where the values change are say A, C, E) and I am ok if the date and time stamps are captured in columns which are consecutive (say I,J,K). Please help.

Thank you

 

[Joe4]

So we don't get caught in a game of Catch-22 (i.e. every time we provide an answer, the question changes), can you just lay it all for us up front?
That is, can you tell us the full extent of exactly what it is you are trying to do (instead of going bit-by-bit)?

Thanks

 

[Joe4]

Is what you posted in your last post is all that you are trying to do, then this should work:

Private Sub Worksheet_Change(ByVal Target As Range)
 
    If Intersect(Target, Range("A:A, C:C, E:E")) Is Nothing Then Exit Sub
        
    If Target = "" Then
        Target.Offset(, 8) = ""
        Exit Sub
    End If
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
        Target.Offset(, 8 - Int(Target.Column / 2)) = Format(Date, "mmm dd yyyy")
        .EnableEvents = True
        .ScreenUpdating = True
    End With
 
End Sub

 

[seethanaboina]

HI, This is exactly whatI need. And it worked. I thought if I understood it for one column, I can replicate it for other columns as required but failed to do so and hence had to ask for additional help.

Thanks

 

[cval]

Hi, what if I wanted to time-stamp a "check in" and a "check out" for data entered. Say for example a tracking number - this code would work for the time "checked in" - but what could i do to record a time for the same tracking number as a "checked out" without having a duplicate entry of the tracking number?

I thought about putting into an array and searching for matching long's but i don't know if that would work.

Any guidance would be appreciated!