Max-Min

Hello Kierco,

Here is a code routine that may prove helpful. Whenever the data stream changes the cells value, the worksheet will compare it against the previous Min and Max values. These values are stored on the worksheet. This way you always have the most current Min and Max values, regardless of the data stream's timing. You will have to provide the code that determines when the stream ends. At that time you can simply retrieve the Min and Max values from the worksheet.

Copy and Paste this code in the Worksheet where your data value will be stored. The code is set to capture the Value in "A1" with Min in "B2" and Max in "C2". You can easily change these in the code to match your needs

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

'Track Min and Max Values of a Cell
 
 Dim ValCell
 Dim MinCell As Range
 Dim MaxCell As Range
  
  ValCell = "$A$2"
  Set MinCell = Range("B2")
  Set MaxCell = Range("C2")
  
  With Target
   If .Address = ValCell Then
   
    If MinCell.Value = "" And MaxCell.Value = "" Then
     MinCell.Value = Target.Value
     MaxCell.Value = Target.Value
    End If
   
    Select Case .Value
     Case Is >= MaxCell.Value
      MaxCell.Value = .Value
     Case Is <= MinCell.Value
      MinCell.Value = .Value
    End Select
    
   End If
  End With
 
End Sub

If you have problems with the code or have any questions, contact me via e-mail at LeithRoss@aol.com.

Sincerely,
Leith Ross

My thanks to MrShorty and Leith for responding to my 'plea' for help on this one. Thanks, guys.

Bruce