Automatically update date when a range of data is changed

**rcvanriet** · 09-11-2012, 07:05 AM

Hello,

I have an excel worksheet that contains columns A to J. Now I'd like to use column J to show the current date if I change any cell in column B to I (in the same row).
On the forum i found a thread that told me how to do this for only one column, so if A is changed, B shows the updated date.
The macro for this is:

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

Can anybody give me advice on how to change these two lines for the purpose i just described?

Many Thanks,

**Jacc** · 09-11-2012, 11:25 AM

How about this?

Please Login or Register  to view this content.

**rcvanriet** · 09-11-2012, 11:52 AM

I tried to do it your way, but it doesn't work. Thanks for your advice anyway

**Jacc** · 09-11-2012, 02:08 PM

It works fine for me but then again I haven't seen the rest of your code.
If you share some more info it should be no problem making it work. Delete things you don't want to share from your workbook and post it with just some sample data.
Or just post more of the code.

**rcvanriet** · 09-12-2012, 03:59 AM

Hhmm would be way more easy to post my excel fil indeed. I added it in the attachment.

Many thanks in advance

**Jacc** · 09-12-2012, 12:11 PM

Does this work for you?

Note that the code is located in a Sheet module. Note also the "Worksheet" and "Change" in the dropdown menus. This setting will give you a Sub with the title

Please Login or Register  to view this content.

Any time a cell is changed on that worksheet this Sub will run and the cell that was changed will be available in the Target variable.
The IF setting just makes sure the code only runs if the Target cell is before the "J" column and after the first row.Code in sheet module, Worksheet Change.PNG

If you right click on a tab and choose Show Code, you will come directly here.

Almost forgot to put the code on display as well

Please Login or Register  to view this content.

**rcvanriet** · 09-12-2012, 12:58 PM

Jacc, works for me now! Might seem very easy, but I found it rather difficult to get used to the code..

Thank you, thank you

Automatically update date when a range of data is changed

LinkBack

Thread Tools

Rate This Thread

Display

Automatically update date when a range of data is changed

Re: Automatically update date when a range of data is changed

Re: Automatically update date when a range of data is changed

Re: Automatically update date when a range of data is changed

Re: Automatically update date when a range of data is changed

Re: Automatically update date when a range of data is changed

Re: Automatically update date when a range of data is changed

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions