Message box every 650 units from today's value

[Ironman]

Hi

A1 contains today's date (if this is needed) and F5 below contains a value.

I'm looking for a message box that will trigger when the value in F5 as at today has increased by 650 and automatically every 650 thereafter.

MrExcel.xlsx
	A
2	8 NOV 2021
Training Log

Cell Formulas
Range	Formula
A2	A2	=UPPER(TEXT(TODAY(),"d mmm yyyy"))

MrExcel.xlsx
	F
5	27,931
Training Log

Cell Formulas
Range	Formula
F5	F5	=10724.97 + SUM(C12:C23357)

Named Ranges
Name	Refers To	Cells
'Training Log'!All_Log_Miles	=OFFSET('Training Log'!$C$12,0,0,'Training Log'!$B$10)	F5
Last90Miles	=OFFSET(Last90Dates,0,2)	F5
Log_LastDistance	=INDEX('Training Log'!All_Log_Miles,ROWS('Training Log'!All_Log_Miles))	F5
LogYear_Log_Miles	=INDEX('Training Log'!All_Log_Miles,MATCH(DATE(LogYear-1,12,31),'Training Log'!All_Log_Dates,1)+1):INDEX('Training Log'!All_Log_Miles,ROWS('Training Log'!All_Log_Dates))	F5
RangeOfDistEntries	='Training Log'!$C$12:OFFSET(LastLogDate,0,2)	F5
'Training Log'!YTD_Distances	=OFFSET('Training Log'!YTD_Dates,0,2)	F5

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F5	Cell Value	between 27000 and 27010	text	YES

Hope you can help?

Many thanks!

 

[Ironman]

I think I'm getting pretty close but I can't get the code quite right. I need the message box to activate when the F5 value is less than 15 miles of 28132 and thereafter every 650 miles from 28132.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim d As Integer
d = 28132 Mod 650  'shoes need changing in 186 miles and every 650 miles thereafter

' warning when shoes are within 15 miles of needing replacing
If 650 - d <= 15 Then MsgBox "You've almost reached 650 miles in your running shoes" & vbNewLine & vbNewLine _
& "Time to buy a new pair!", vbInformation, "Running Shoes Worn Out"

End Sub

Hope you can help?

Thanks again!

 

[Ironman]

I was way off course. I've just read up on the Mod element and it's not relevant for what I need.

Is there any way that a message box can be activated when the value in F5 is 15 or less from 28132 and thereafter every multiple of 650 from 28132?

 

[NoSparks]

Is there any way that a message box can be activated when the value in F5 is 15 or less from 28132 and thereafter every multiple of 650 from 28132?

F5 won't trigger the Worksheet_Change event because F5 has a formula.
Regarding the use of MOD, maybe something along the lines of this would work

Dim d As Long
d = [f5] Mod 650
If d > 166 And d < 182 Then
    MsgBox "Within 15 of reaching 650 increment"
ElseIf d = 182 Then
    MsgBox "Exactly 650 increment"
ElseIf d > 182 And d < 197 Then
    MsgBox "Within 15 past 650 increment"
End If

 

[Ironman]

SUPERB! That works perfectly, thanks a lot Nolan! - so it did work after all using MOD.

Can you tell me why Excel doesn't treat formulas as values when using code - is it because the worksheet hasn't changed with a user input? Shouldn't Excel be able to recognise any change?

 

[Joe4]

Can you tell me why Excel doesn't treat formulas as values when using code - is it because the worksheet hasn't changed with a user input? Shouldn't Excel be able to recognise any change?

The "Worksheet_Change" event procedure runs automatically on the manual update of some cell.
The "Worksheet_Calculate" event procedure runs whenever any value on the sheet is recalculated.

So you would typically use "Worksheet_Calculate" to identify when a calculation has changed. The caveat with "Worksheet_Calculate" is that it cannot determine what cell's calculation changed, only that some calculation was changed (hence, there is no "Target" argument for "Worksheet_Calculate", like there is for "Worksheet_Change").

 

[Ironman]

Thank you Joe, that was helpful.