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 $B$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