automatic updating cell

[j feka]

I'd like to create a function that will automatically
store a new high value of another cell when it occurs,
but will not change if that first cell's value drops.

I've tried something like

Function NuHi(OldHi, PriceNow)
NuHi = OldHi
If PriceNow > OldHi Then
NuHi = PriceNow
OldHi = PriceNow
End If
End Function

But this won't work. If I remove the
OldHi = PriceNow line, the function will take a value
from another cell which is put in the OldHi parameter,
but if that value goes down, the function gives a lower
value.

Can anyone help?

 

[Trevor Shuttleworth]

Try this:

Function NewHigh(checkCell As Range)
Static oldhigh
NewHigh = oldhigh
If checkCell.Value > oldhigh Then
oldhigh = checkCell.Value
NewHigh = checkCell.Value
End If
End Function

Regards

Trevor

 

[jfeka]

Thanks Tevor,

I don't think this will do the trick because the oldhigh
will be there when I want to use the function in a
different cell.

Simple example of what I want to do.

In cell B1 store a high value, then if A1>B1 replace the
value in B1 with A1.

Now, using the same function I will want to test

A2 against the value in B2
and
A3 against the value in B3

Each row may have totally different high values.

Jack

 

[j feka]

Thanks for the try Trevor, but unless I'm mistaken your approach won't
work if I have more than one oldhigh.

Suppose I have Newvalue in cell A1 and the stored HighValue in cell B1.
If I use the function NewHigh(A1) in B1 the value which is in there will
be tested against oldhigh.

Now suppose I have another Newvalue (different value) in A2 and I want
to test it against a value which is unique to that row which I store in
B2. If I put the function NewHigh(A2) into B2, won't I be testing it
against the value which was established in Row 1?

I intend this function to work in multiple rows, not just one or two.

Am other ideas?
Jack