Timestamp beneath target cell when target cell's value changes based on calculation.

Tracker_Pipeline_and_Accruals_04.14.2016.xlsx

In the 3rd sheet ('Accrual!'), cells B11 and B23 contain boxes for totaling their respective charts.

I am trying to write a VBA script that will simply timestamp [MM:DD:YY HH:MM:SS AM/PM] in B12 and B24 anytime their above cell's values change.

I am having trouble with it because I don't want the timestamps to update anytime I open the WB, or anytime a change is made in other cells - I only want a timestamp in B12 when the value of B11 changes, or in B24 when B23 changes.

I have done this before, but it took me about a week of grueling shots in the dark. If someone is willing to explain how their code works, I am definitely trying to become more independent.

Thanks all in advance.

There are a couple of ways to do this. One is to use Worksheet_Change to monitor for changes to the cell of interest. However, in your case that doesn't work because you are using a formula; the event fires only if the content of the cell changes, not the value it returns. Another way is to monitor changes to the cells that can cause your formula result to change, but there is a long chain to do that.

So this method records the value in the cell, and every time anything in the file changes, it checks to see if that cell changed. It keeps track of prior values in a sheet called Track, which can be hidden if you prefer.

You can do it with a Worksheet Change event handler but, because in this case the cell of interest is a formula, it is necessary to monitor the cells's precedents, not the cell itself.

Please Login or Register  to view this content.

Note:

Please Login or Register  to view this content.

@6SJ: thanks for the rep