Code to capture recalculated cells - "Date last modified" utility

**Tristan** · 02-14-2008, 07:06 AM

Hi,

I'm trying to work out how to code a field so that if a cell (in a record row) is changed then in that row the value for "Date Last Modified" is updated with the current date.

Using the Worksheet_Change event handler I can do all this easily enough (see code below). However this won't work for cells that are updated by formulas. Has anyone got any ideas of how I can capture those cells that change when they are recalculated.

Thanks for any suggestions,

Tris

Please Login or Register  to view this content.

**Tristan** · 02-15-2008, 07:53 AM

**royUK** · 02-15-2008, 10:05 AM

Maybe you can use something similar to this

Please Login or Register  to view this content.

**Tristan** · 02-15-2008, 11:56 AM

Hi Rob - thanks for the reply,

I see what your code is doing and I guess I should try and explain better what i'm trying to achieve.

If you can imagine I have a few rows of records, the final cloumn holding a date value "Date Last Modified".
The code in my first post will update the Date value if I change any cells in that row.
Now, if you can also imagine that one of the cells in that row contains a formula. This formula is a simple Lookup function, pulling in a value from a table based on the value of some other cell. If I was to change the value of that cell in hte lookup table, then all those records which are pulling in that value through the lookup function will be altered. I can't seem to capture that event i.e. my Date Last Modified column won't update based on cells recalculating - only "manual changes".
I can't use the cell in the table that was originally changed as a tag to work out which cells must be changed which is essentially what your proposed code is doing.
I can do it with some rather laborious coding but I'd like a simpler method. Maybe having a "mirror" column where I can compare before and after events will do it but that's kinda messy.
What do you think? - does what I'm explaining make sense?

Thanks for your help
Tris