Message Box when cell value becomes largest in range

[Ironman]

Hi

Sheet 'Training Log' D8413:D8657 contains values (not formulas) that are being added to daily, down to D8779.

I need a msgbox to pop up once only, when a value is added that becomes the new maximum value in the range D8413:D8779 (and then pop up again the next time a new maximum value is input).

Many thanks!

 

[Eric W]

Try this. Open a copy of your workbook. On your Training Log sheet, right click on the sheet tab on the bottom and select View Code. Paste this code in the window that opens:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyData As Variant, OldMax As Double

    If Intersect(Target, Range("D8413:D8779")) Is Nothing Then Exit Sub
    
    MyData = Range("D8413:D8779").Value
    MyData(Target.Row - 8413 + 1, 1) = ""
    OldMax = WorksheetFunction.Max(MyData)
    If Target.Value > OldMax Then
        MsgBox "You just entered a new max value: " & Target.Value & vbCrLf & "The old max was: " & OldMax
    End If
    
End Sub

Close the VBA editor (Alt-Q or the red X). Try it out.

 

[Ironman]

Superb Eric, that works perfectly, thank you!

Can I just ask you if it's possible for you to also include C8413:C8779 in the code and for a different msgbox to be created for each of the ranges (eg. "maximum distance achieved" for C8413:C8779 and "maximum time achieved" for D8413:D8779), or would I need to copy and add the above to the Worksheet_Change and change the range?

Thanks again Eric!

 

[Eric W]

OK, try this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyData As Variant, OldMax As Double

    If Not Intersect(Target, Range("C8413:C8779")) Is Nothing Then
        MyData = Range("C8413:C8779").Value
        MyData(Target.Row - 8413 + 1, 1) = ""
        OldMax = WorksheetFunction.Max(MyData)
        If Target.Value > OldMax Then MsgBox "Maximum distance achieved"
    End If
    
    If Not Intersect(Target, Range("D8413:D8779")) Is Nothing Then
        MyData = Range("D8413:D8779").Value
        MyData(Target.Row - 8413 + 1, 1) = ""
        OldMax = WorksheetFunction.Max(MyData)
        If Target.Value > OldMax Then MsgBox "Maximum time achieved"
    End If
    
End Sub

 

[Ironman]

Ah, that's fantastic! Thank you ever so much Eric

 

[Eric W]

Happy to help! ?