Add date and time stamp

[100338042]

Hello!
I found this code here-under in the archives of mrexcel.com and it works great.
The code basically records changing values in an excel cell.

Upon detecting a change in the specified cell value, this code records the new value in another cell, and basically keeps a log of changes.
I wish to add a 'date and time' stamp each time a new value is logged.
So basically I wish to have a log of the change in value and the time it happened.

Grateful for you suggestions please
Thank you - Alex


The code so far:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub 'Only interested in cell A1
Range("B65536").End(xlUp).Offset(1, 0).Value = Target
End Sub


Source:
Excel Challenging! How to record changing values in a cell

 

[par60056]

Welcome to the forum.

If I understand what you want, you can try this:

[COLOR=#333333]Private Sub Worksheet_Change(ByVal Target As Range)[/COLOR]
[COLOR=#333333]If Target.Address <> "$A$1" Then Exit Sub 'Only interested in cell A1[/COLOR]
[COLOR=#333333]Range("B65536").End(xlUp).Offset(1, 0).Value = Target[/COLOR]
[COLOR=#333333]Range("C65536").End(xlUp).Offset(1, 0).Value = now[/COLOR]
[COLOR=#333333]End Sub

[/COLOR]

 

[mole999]

something like

With Range("B65536").End(xlUp)
.Offset(1, 0).Value = Target
.Offset(1, 1).Value = Now()
End With

 

[100338042]

Thank you both - worked fine )

I am tryting to understand the code itself because I wish to change the location where the log (of the changed cell value and the time stamp) is generated. At the moment the log automaticlaly starts generating at cell B2 but I wish to have the possibility to have it start at cell F17 for example.

Grateful for your suggestion please,
Thank you - Alex

 

[mole999]

Range("B65536").End(xlUp).Offset(1, 0).Value = Target

that fixes your column, the offset the row

you could

Range("K65536").End(xlUp).Offset(1, 0).Value = Target or

Range("B65536").End(xlUp).Offset(1, 9).Value = Target

i think

 

[100338042]

Thank you mole999 !
I experimented with the Range("B65536") and by changing the first letter in the brackets I managed to determine the column where the log is created.
The chosing of the row is proving more tricky. When I experimented with the offset property it changes the spacing between the last entry log and not the location of the log :S

Thank you and regards - Alex

 

[par60056]

The "Range("B65536").End(xlUp)" tells excel to go to cell B65536 (this used to be the last row prior to Excel 2007) and search up until if finds a non blank cell. If Column B is completely empty it returns "B1" The Offset(1,0) says to reference the cell 1 row down (so you don't overwrite) and 0 columns to the right.

If you put a header in F16 like "Log", then you can use Mole's code substituting the F for the B. The Offset(1,1) will put the timestamp in columns G.