VBA help - recording highest and lowest odds

[Captain Sensible]

Hoping to get some vba code to record the highest and lowest odds for all selections over a time period. I have some code that'll copy and paste the current odds to a cell but I'd like to keep the minmum and maximum odds rather than a snapshot of the odds at a particular time. Does anyone have any similar code or know if it's possible to do with excel ?

Thanks

[phrenetic]

Here's what I use:

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iRow As Integer
Dim iCol As Integer
Dim cell As Object

For Each cell In Target
  iRow = cell.Row
  iCol = cell.Column

  ' Update Max and Min Back Values
   If iCol = 6 And iRow > 4 And iRow < 46 Then
    Application.EnableEvents = False
    If IsEmpty(Range("AJ" & iRow).Value) Then Range("AJ" & iRow).Value = 0
    If IsEmpty(Range("AI" & iRow).Value) Then Range("AI" & iRow).Value = 1001
    If Not IsEmpty(cell.Value) Then
      If cell.Value < Range("AI" & iRow).Value And cell.Value > 1 Then Range("AI" & iRow).Value = cell.Value
      If cell.Value > Range("AJ" & iRow).Value And cell.Value < 1001 Then Range("AJ" & iRow).Value = cell.Value
    End If
    Application.EnableEvents = True
  End If
 
  ' Update Max and Min Lay Values
  If iCol = 8 And iRow > 4 And iRow < 46 Then
    Application.EnableEvents = False
    If IsEmpty(Range("AL" & iRow).Value) Then Range("AL" & iRow).Value = 0
    If IsEmpty(Range("AK" & iRow).Value) Then Range("AK" & iRow).Value = 1001
    If Not IsEmpty(cell.Value) Then
      If cell.Value < Range("AK" & iRow).Value And cell.Value > 1 Then Range("AK" & iRow).Value = cell.Value
      If cell.Value > Range("AL" & iRow).Value And cell.Value < 1001 Then Range("AL" & iRow).Value = cell.Value
    End If
    Application.EnableEvents = True
  End If
Next cell
End Sub


Columns AI and AJ contain the min and max back values for each horse; Columns AK and AL contain the min and max lay values for each horse.
Rows 5 to 45 contain the details about each runner (allows for 41 runners!) in my spreadsheet.

Hope this helps.

Alistair

[Captain Sensible]

Thanks this is exactly what I was looking for.