Automatic Update

dlomas

New Member
Joined
Apr 28, 2002
Messages
22
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.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top