I'm trying to rewrite a sort of the "track changes" feature of Excel using
VBA.
The method I'm following is to store in a variable the value of the active
cell/range every time the application event "selection change" is triggered.
Then I compare changes versus the previous stored variable when the
application event "sheet change" happens, and so write eventual changes in
formulas/values to a separate worksheet.

The procedure above seems to work fine when changing the values into cells
using "normal" methods like just writing the new value into a cell or
copying and pasting even complex ranges (not just single cells).

However, I noticed it doesn't work when using the autofill method: dragging
(or double clicking) the small dot at bottom right corner of the selection
marker.
This is due because in this case the "change" event is triggered BEFORE the
"selection change" event is triggered, and not after as in "normal" methods.
So in this case the application cannot store the values/formulas the cells
had before they were changed, thus the application think no change happened.

I wonder whether there's a complete different way for doing that, like
having a method of the range object that is passed to the "Change" event
that stores the value/formula the range had before the change.

That's the SheetChange event declaration I'm using. Maybe I'm missing
somthing?
Private Sub App_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Do you have any idea for solving the above issue?
Many thanks for your help.

Paolo
Milan, Italy