Is there a way to...

[sagar]

... to keep track of the highest and lowest prices in a football game for all three selections via Visual Basic?
I've being struggling with it in the past few days, with scarce results. Can anyone help me?

T.

[Captain Sensible]

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 Cells(1, 36) = 1 And iCol = 15 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 IsEmpty(Range("AH" & iRow).Value) Then Range("AH" & iRow).Value = Range("O" & iRow).Value

If Not IsEmpty(cell.Value) Then
Range("AG" & iRow).Value = Range("O" & iRow).Value
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



Next cell

End Sub



This will log the highest and lowest values of the last price matched

[sagar]

Many thanks for this, it's very kind of you. I tried to copy the copy in VBA (in the first sheet) but prices are not logging. I'm sure that your code is fine and I'm doing a mistake - any idea why it's not working?
Thanks!

[Captain Sensible]

Ooops just copied and pasted it from one of my sheets, amend the line

If Cells(1, 36) = 1 And iCol = 15 And iRow > 4 And iRow < 46 Then

to

If iCol = 15 And iRow > 4 And iRow < 46 Then



Cells(1, 36) = 1 referred to something in one of my sheet so it only logged in play odds

[Captain Sensible]

Best just clarify what it does

It'll log values into the following columns

AG it'll enter the last odds matched
AH it'll stick the starting odds (i.e. the last price matched figure when the sheet was loaded up, this won't change unless no price has been matched then it'll show 0 until a price has been matched)
AI will show the lowest price matched, if no odds have been matched it'll show as 1001
AJ will show the highest odds matched, if no odds matched 1.01 will show

[sagar]

It works perfectly now. Many many thanks!

[Captain Sensible]

No problem sadly I haven't a clue about vba and one of the vba bods on here, phrenetic, kindly wrote it for me so more than happy to pass it on.