Automatic Update

[dlomas]

One of my co-workers is creating a spreadsheet with a list of values in one column and the date when these values changed in another column. Is there a way to code the date column to automatically change to the current date when the value changes? Any help would very much be appreciated.

[Damon Ostrander]

Hi dlomas,

In order to answer your question it is necessary to know HOW the values in the first column are changing. Are they changing

1. by being manually entered/edited?

2. because they contain a formula that makes them dependent on another cell that changes?

3. as a result of being set by a VBA macro or event?

In addition, do you want to capture changes to the cell's formatting? Or just to its contents?

Damon

[dlomas]

Damon, The cells will be manually edited and it's just the contents of the cell that we want to capture the changes of. Thanks for your help!

[Damon Ostrander]

Hi again Dlomas,

Okay, if you just want to capture manual entries, this is very easy. Here's sample code that will record in column C the date that any value in Column B is manually edited. Be sure to format column C in a date format.

Install this code in the worksheet's event code module. To do this, right-click on the worksheet tab, select View Code, and paste this code into the VBE code pane that appears, and voila!

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
'store current date/time in column 3
Cells(Target.Row, 3) = Now()
End If
End Sub

If you want to further restrict this routine to looking at just on a certain range of rows in column 2, the If test would look like:

If Target.Column = 2 AND Target.Row > 5 AND Target.Row < 20 Then

Which would look at only B6:B19. Much more complex ranges can be accommodated using the Intersect function.

Enjoy and