Capture Mximum Value ever in a range

I have a range (A1: A500) with values which is continually updated with fresh entries. I need to capture the maximum value ever entered in the range and reported by the formula =Max(A1:A500) in Cell B1.

For instance, if for a start, =MAX(A1:A500) produces in $B$1 250 and subsequently 330, 600,100 and 175 are the maximums produced following new entries in A1:A500, I will expect Cell B1 to show 600 from the time that figure was generated by the Max function. (The ouput in $B$1 will therefore be in the sequence 250, 330, 600,600,600 with a change occurring only when Max(A1:A500) turns up a figure greater than 600. But even if the Range A1:A500 is at any stage cleared of all entries, the value returned in $B$1 should still be the highest Maximum value ever recorded (and not zero).

Here is my Worksheet Event handler which falls short of achieving the objective.
With the formula =MAX($A1:$A500) in $B$1

Private Sub Worksheet_Calculate()
On Error Resume Next

With Range("B1")
.AddComment
If Range("b1").Value < .Comment.Text Then Range("b1").Value = .Comment.Text
.Comment.Text Chr(10) & Range("b1").Value
End With
End Sub

The shoercomings on my code:

Max values in A1:A500 are retained in $B$1 as well as the Comment Box. But if low entries are made in the range to override high values, the resultant reduced Max values appear in $A$1 as well as the Comment Box. My intention is to check any new value in $B$1 against the CommentBox Value designed to be "previous highest Max" and if the new value is lower than the previous (CommentBox value), such (new) value is discarded and supplanted by the previous.
The plan sounds good but is defying excution. Any help is welcome. TIA

Here's how I would approach this. It's untested, but I think it should work:

1) Put your MAX function in some out of the way cell. For my example, I'll use Z100
Z100=MAX($A$1:$A$500)

2) Now in your event handler do something to the effect of:
If Range("b1").value < range("z100").value then range("b1").value=range("z100").value


Now the value in z100 is updating everytime the worksheet updates, but your eventhandler is only putting the new max in b1 if the new max is greater than the old max.

Does that work?

shorty,

I have some conceptual problem with the solution. If B1 starts off as a blank, as indeed it should, the event handler IF CLAUSE If Range("b1").value < range("z100").value then range("b1").value=range("z100").value will simply make $B$1 a surrogate of $Z$100-sharing the same values!

May be I am missing something.

David

In that case first check if b1 is blank and if not then proceed.


if not IsBlank(Range("b1")) then
If Range("b1").value < range("z100").value then range("b1").value=range("z100").value
'....
'....
else
'.....
'....
end if

Mangesh

Originally Posted by davidm

shorty,

I have some conceptual problem with the solution. If B1 starts off as a blank, as indeed it should, the event handler IF CLAUSE If Range("b1").value < range("z100").value then range("b1").value=range("z100").value will simply make $B$1 a surrogate of $Z$100-sharing the same values!

This would be true the first time through, because any maximum in Z100 will be greater than 0 (unless it's possible for all of the data in A to be negative).

I'm pretty sure that VBA will assume a BLANK is equivalent to zero for the purposes of making the comparison.

Step through with me:
1) column A is blank, B1 is blank, Z100 evaluates to 0
2) Column A is propagated with data, Z100 evaluates to the MAX of thes data, eventhandler compares BLANK/0 to value in Z100 and places the larger of the two values in B1 (1st time around this should automatically be the value in Z100)
3) Column A propagates again, Z100 evaluates to MAX of these new data, eventhandler compares value in B1=old max to value in Z100=new max. If old max (B1) is smaller than the new max (Z100), then place the value in Z100 into B1. Otherwise, leave B1 alone until the next time.

Unless you are clearing B1 in between each propagation of column A, it should work.

Does that make more sense? Is that an accurate representation of what you want the spreadsheet to do?

One simple fix would be a conditional format so that the cell changes color for the max number in the range. Just a thought.