Maximum and Minimum of a cell that changes

[dan]

I made a spreadsheet that tracks my stocks by regularly updating cells with
the current price of the stock, etc. So my profit or loss of a given stock
is displayed in a cell, and then when I tell Excel to update, that value
chagnes. I have been trying to make a column that shows the historical
all-time high and low for this value unsuccessfully. My problem seems to be
when the value in the cell goes from positive to neagative or vice versa.
How can I write a function to show the historical maximum and/or minimum of a
cell that is periodically updated.

 

[dan]

The price cell is updated via data feed. The cell that I am trying to find
the max/min for is calculated by a formula that uses that updated price cell.
I have no problem getting my cell in question to show the max historical
value as long as the referenced cell is positive, but if the value in that
cell is negative, all it returns for the historical max is a zero.

 

[Gary''s Student]

Here is a macro example. A1 is refreshed externally via user command. B1
will contain the historical minimum, C1 will contain the historical maximum:

Sub limitChecker()
Dim a As Range, b As Range, c As Range
Set a = Range("A1")
aa = a.Value
Set b = Range("B1")
Set c = Range("C1")

If b.Value = "" Or c.Value = "" Then
b.Value = aa
c.Value = aa
Else
If aa < b.Value Then
b.Value = aa
End If
If aa > c.Value Then
c.Value = aa
End If
End If
End Sub

adjust the cells to suit your needs.