Excel Digest

A few months ago a user asked this question in Excel Help Forum:

If you have two columns, is it possible to have column B put in the date that data was added to column A?

I have replied to the question in the forum at that time, and I have noticed since then that other users are asking the same question. For that reason I thought it might be a good idea to elaborate on the subject here.

To insert an update date or a timestamp in a cell, you need to use a worksheet event called “Worksheet Change”. Just follow these steps:

1. Switch to visual basic editor by clicking Tools –> Macro –> visual basic editor.

2. Click on the name of your sheet in project explorer at your left.

3. On the right pane select “Worksheet” from the left drop down and select “Change” from the right drop down.

4. Now you are in the Worksheet_Change event.

5. Copy this code between “Private Sub Worksheet_Change” and “End Sub”.

Col = Left(Target.Address, 2)
If Col = “$A” Then Target.Offset(0, 1) = Now

Now whenever you change a cell in column “A” the next cell in column “B” will be updated with date and time.

You can change the code to suit your own needs, but the main idea is that you have to use the “Worksheet Change” event for this purpose.