vba to recognise change in cell value created by a formula

I have taken this code from this forum (Thank you Kaper) and it does what I need apart from the fact that when the cell value changes it is the result of a formula calculation and the vba does not pick it up. If I change the appropriate cell (C3) manually it works fine.

I would very much appreciate some help with this ...

Thanks
Paul

Please Login or Register  to view this content.

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #3 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer

There are two ways I know of to do this.

One is to use a static variable to store the value of C3, then use the Worksheet_Calculate event to detect a change, and when a change occurs compare the new value to the stored variable and see if it's different. This technique is illustrated in another thread.

Another is to use Worksheet_Change to detect changes in the precedent cells in the C3 formula. What is the formula?

Do D3 or D34 have formulas? If not then you can use this. I assume your code above is in a Worksheet module, and if so would be updated as follows:

Please Login or Register  to view this content.

The other technique requires a couple of other bells and whistles so I'm starting with this.

As I was involved in original thread let me comment:

Originally Posted by 6StringJazzer

Do D3 or D34 have formulas? If not

That's the important part. If in either D3 or D34 you have formulas then you could try to check again which cells are real "source of change" and if these cells are located in the same worksheet try to use their addresses in Worksheet_Change code.

Otherwise (I expect this will be the case, - probably in D24 you do have a sum, don't you?) adopt this very compact code from link mentioned above: http://www.excelforum.com/excel-form...g-formula.html


PS. I'd stick to checking if Target intersects with interesting cells. Otherwise, if we chaeck if target is one of particular cells, then if for instance someone copies a value into a range D2:D4 the code will ignore it (target is not D3).
So my suggestion for second line in above code would be:

Please Login or Register  to view this content.