One of the reasons Worksheet_Change might bog down your system is because you're making changes to the Worksheet from within the Worksheet_Change event. As these changes trigger the Worksheet_Change event... you can end up with a considerable amount of overhead, as it recursively calls itself. Excel eventually detects this (sometimes), and stops the process, but it eats up considerable CPU.
In the future, if you decide to use the worksheet change macro, put the following code around the section where you make changes to your worksheet:
Application.EnableEvents = False
[code to make changes]
Application.EnableEvents = True
Anyway, as to your problem....
One way to do what you as is to use Circular Referencing. It's not something to be done casually though. You have to remember you've done it, and there are no warnings after you've set it so that it catches 'accidental' circular references.
First, adjust your settings:
Tools -> Options -> Calculation
- Check the iterations box
- Set the min and max iterations to 1
Essentially, if you want to track the minimum value of a cell (say A1), you can put the following formula in B1 (Assumes you'll never hit 0).
=MIN(IF(A1:B1=0,"",A1:B1))
Entered with CTRL+SHIFT+ENTER
As you can see, this is a circular reference. It will need to be an array formula because B1 (where you enter the formula) has an initial value of 0, which can screw things up... ie. it takes 0 as the value, no matter what is in A1.
Maximums are easier, assuming you don't have negatives (that is, if you have negatives, you'll have to set up something like the above to avoid 0 values):
As to a VBA solution, I'd be inclined to use the Worksheet_Change macro, but that doesn't meet your specs.
Scott
Bookmarks