Date and Time that a cell was populated

[SwimmingNathan]

Hey hey,

Is there a way for a date and time to be added into say B1 when any data is added into A1?

I could swear I've had this running before, when I've been adding rows of contacts, in order to let me know what dates batches were added.

I cannot for the life of me remember/ figure out how though now..!

Any help is greatly appreciated.

Cheers,

Swimming

 

[bruderbell]

Give this a try

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    
        ' The variable KeyCells contains the cells that will
        ' cause an alert when they are changed.
        Set KeyCells = Range("A1")
        
        If Not Application.Intersect(KeyCells, Range(Target.Address)) _
               Is Nothing Then
    
            ' Display a message when one of the designated cells has been
            ' changed.
            ' Place your code here.
            With Target.Offset(0, 1)
                .Value = Now()
                .NumberFormat = "[$-409]d-mmm-yyyy hh:MM:ss AM/PM"
            End With
        End If

End Sub

 

[SwimmingNathan]

Thanks for that, I'll try to get it to work, I'm not a master of VBA though, weird thing is I'm sure I've done this without even using a formula!

 

[bruderbell]

The code above (harvested from a Microsoft KB on the topic and tweeked minorly) will meet the need you describe in the original question. Just change the KeyCells range to include whatever cells you want the macro to watch and act on. If you wanted the macro to act whenever any cell in column C is changed, you might change it to

        Set KeyCells = Range("C:C")

(as an example).

 

[SwimmingNathan]

Ok thank you again, like I said I'm not a master of VBA (or Excel for that matter) how do I set which cell the date and time will be displayed in?

 

[bruderbell]

The offset part tells it where to put the timestamp. Offset(0,1) means that when a target cell is changed, the date/time will go in the cell 0 rows down and 1 cell to the right.

Glad I could help

 

[SwimmingNathan]

Ah I see, thanks a bunch