Msgbox when any one of a number of subtotals has been exceeded

[Ironman]

Hi

A2:A243 (which is added to regularly) contains values and subtotals for each year.

The subtotals for each year are identified in blue and by a formula in Column B.

I need a message box to appear when the value in cell A245 exceeds a subtotal.

If a trigger cell is needed, then C245 can be used (so I can easily see it), although this will need to change when a new entry is added.

Many thanks!

Book1
	A	B
210
211	136	Tue 28/01/2020
212	152	Wed 05/02/2020
213	144	Sun 09/02/2020
214	166	Thu 13/02/2020
215	156	Mon 17/02/2020
216	100	Fri 21/02/2020
217	98	Tue 25/02/2020
218	97	Sat 29/02/2020
219	126	Wed 04/03/2020
220	142	Sun 08/03/2020
221	103	Thu 12/03/2020
222	11	TOTAL FOR 2020
223
224	135	Tue 22/06/2021
225	57	Sat 26/06/2021
226	154	Wed 30/06/2021
227	7	Sun 04/07/2021
228	120	Mon 12/07/2021
229	124	Fri 16/07/2021
230	51	Tue 20/07/2021
231	162	Sat 24/07/2021
232	72	Wed 28/07/2021
233	101	Sun 01/08/2021
234	3	Thu 05/08/2021
235	110	Mon 09/08/2021
236	40	Fri 13/08/2021
237	77	Tue 17/08/2021
238	44	Sat 21/08/2021
239	68	Wed 25/08/2021
240	2	Sun 29/08/2021
241	89	Thu 02/09/2021
242	4	Mon 06/09/2021
243	76	Fri 10/09/2021
244
245	20	TOTAL FOR 2021
Iron Man Log

Cell Formulas
Range	Formula
A224:A243,A211:A221	A211	=RANK(D211,$D$2:$D$245)
A222	A222	=COUNT(A211:A221)
B222	B222	="TOTAL FOR " & YEAR(B221)
A245	A245	=COUNT(A224:A244)
B245	B245	="TOTAL FOR " & YEAR(B243)

Named Ranges
Name	Refers To	Cells
Iron_Mans_2020	='Iron Man Log'!$A$211:$A$221	A222

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A210	Cell Value	=""	text	NO
A245	Cell Value	=""	text	NO
A244	Cell Value	=""	text	NO
A222	Cell Value	=""	text	NO
A223	Cell Value	=""	text	NO
A2:A6,A211:A221,A182:A208,A177:A179,A153:A174,A149:A150,A138:A146,A112:A135,A98:A109,A90:A95,A87,A78:A84,A75,A68:A72,A58:A65,A55,A50:A52,A38:A43,A35,A23:A32,A9:A20,A224:A243,A46:A47	Cell Value	=1	text	YES
A2:A6,A211:A221,A182:A208,A177:A179,A153:A174,A149:A150,A138:A146,A112:A135,A98:A109,A90:A95,A87,A78:A84,A75,A68:A72,A58:A65,A55,A50:A52,A38:A43,A35,A23:A32,A9:A20,A224:A243,A46:A47	Cell Value	=2	text	YES
A2:A6,A211:A221,A182:A208,A177:A179,A153:A174,A149:A150,A138:A146,A112:A135,A98:A109,A90:A95,A87,A78:A84,A75,A68:A72,A58:A65,A55,A50:A52,A38:A43,A35,A23:A32,A9:A20,A224:A243,A46:A47	Cell Value	between 3 and 10	text	YES
A2:A6,A211:A221,A182:A208,A177:A179,A153:A174,A149:A150,A138:A146,A112:A135,A98:A109,A90:A95,A87,A78:A84,A75,A68:A72,A58:A65,A55,A50:A52,A38:A43,A35,A23:A32,A9:A20,A224:A243,A46:A47,A246:A531	Cell Value	=""	text	NO

Cells with Data Validation
Cell	Allow	Criteria
A245	Whole number	=12345
A222	Whole number	=12345

 

[Michael M]

That's because loks for the above 3hr runs and the below 3hr run.....if one column doesn't change it will still return the previous result of that column....that way you only need 1 lot of code !

 

[Ironman]

Understood, but the other message box isn't correct though because the Col C figure for 2021 relates to Col A value of 20, but the 2007 figure is 22, yet it says it's exceeded it, which it hasn't, plus the ranking can't have changed.

One other glitch is the boxes each appear twice, although that may be because I double clicked a protected validated cell and it gave a validation error message and then I got 2 lots of your code messages instead of 1?

 

[Ironman]

PS - I also got the msgbox for >3hr runs for 2021 is greater than 2004 yet 2021 = 4 and 2004 = 5?

 

[Michael M]

Why is there a formula in D247 now ??

 

[Ironman]

Er, that's the data for 2021. I think I inserted another row above the table, apologies.

Here's the up to date table:

Book1
	A	B	C	D	E	F
220	4	Mon 06/09/2021	15.1	3:46:24	15:00	1:30pm, 20-21ºC, cloudy, warm and sunny, light breeze. 11 hours of blissful sleep without being woken by that ****ing dog, then ate banana and 3 pts of squash (knew it would be warm out). Perforated t-shirt, racing shorts - perfect. Vaselined fingers, no nail-biting. Wanted to do this route but when I got out into the warm sunshine I thought I'd better play safe and go up the canal so I know how far I'll be going, but as I was approaching The Fisherman's I thought "sod it, I'm going right". And no regrets, it was pure, fresh eye candy, SO much more interesting, "this is the future of the canal runs" I thought. Much nicer setting, no monotony, so many landmarks. Complimented a beautiful woman on her nice fragrance wafting way behind her as I passed her! Echoes playing on Walkman as I approached the 2 imposing halves of Salts Mill either side of the canal - very atmospheric! Despite the views, lower back ached for the full length of the canal section. R nipple chafed from the sweat. Had planned to go back over the 3-Rise but when I saw the potential mileage I decided to go up to the top of the 5-Rise and back down (measured 0.34M from 3-Rise to top of 5-Rise - exactly the same as from home up to the junction of Bents Ln!). Wasn't as hard as I'd imagine it'd be while I was running towards Saltaire. Left hamstring got really tight from about 10M but just about held my normal stride. Got increasingly tired over last 5M but as ever - my eyes were pleading for encouragement from oncoming walkers (one or two women did!), but my head and my quads strength saw me through. My first words when I got back? "Jesus". "Jesus". I don't know how I did it. Well I do know - with a super powerful mind and strong quads from all the biking! Very satisfying run.
221	76	Fri 10/09/2021	10.6	2:32:42	14:24	3:00pm, 20-18ºC, bit humid, one or two light but welcome showers. Heart bit tired B4 setting out. Vaselined fingers, no nail-biting again! Forgot to wear bandana (they were both on clothes horse upstairs) and regretted it, heavy sweating and rain were a PITA. Wore tracksters and perforated t-shirt. Pee stop junction of Old Allen Rd/Back Ln. R nipple really chafed despite vaselining it B4 setting out (bloke in car with little girl in front passenger seat going my way stopped car alongside me near bottom of Greenside Ln and asked on behalf of the little girl (!) if I was OK - assumed it was cos of me crawling up the road until I got in and saw all the blood/sweat staining down my t-shirt). I'm renaming Lee Ln/Cross Ln junction "Hungry Corner" cos more than once now I've felt hungry as I approach it! L hamstring became tight, almost normal now. Overall, run felt like a bit of a slog, probably combination of hard bike session yesterday (increased to Level 8, a lot harder) and in bed at 4am then woken 50 mins later by that ****ing dog and again at 11am.
222
223	198	<<< TOTAL No. IRON MAN RUNS 1984-2021
224	TOT	YEAR	RK/23	No. >3HRS	YEAR	RANK/23
225	5	1984	14	0	1984	12
226	12	1986	5	2	1986	7
227	10	1987	8	1	1987	9
228	1	1988	20	0	1988	12
229	6	1989	12	0	1989	12
230	2	1990	18	0	1990	12
231	3	1991	16	0	1991	12
232	1	1993	20	0	1993	12
233	8	1994	10	5	1994	1
234	5	1996	14	0	1996	12
235	1	1998	20	0	1998	12
236	7	2000	11	1	2000	9
237	1	2001	20	0	2001	12
238	6	2002	12	0	2002	12
239	12	2003	5	5	2003	1
240	24	2004	2	5	2004	1
241	9	2005	9	2	2005	7
242	2	2006	18	0	2006	12
243	22	2007	3	4	2007	4
244	3	2008	16	1	2008	9
245	27	2019	1	4	2019	4
246	11	2020	7	0	2020	12
247	20	2021	4	4	2021	4
Iron Man Log

Cell Formulas
Range	Formula
A220:A221	A220	=RANK(D220,$D$2:$D$223)
E220:E221	E220	=D220/C220
A223	A223	=COUNT(A2:A222)
B223	B223	="<<< TOTAL No. IRON MAN RUNS 1984-" & YEAR(B221)
C224	C224	="RK/"& COUNT(A225:A248)
C225:C247	C225	=RANK(A225,$A$225:$A$247)
D247	D247	=RANK(A247,$A$225:$A$247)
F224	F224	="RANK/"& COUNT(A225:A248)
F225:F247	F225	=RANK(D225,$D$225:$D$247)

Named Ranges
Name	Refers To	Cells
Iron_Mans_2020	='Iron Man Log'!$A$190:$A$200	A223
IronManLogLastYrTot	='Iron Man Log'!$A$246	F224, C247:D247, C224:C246

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D222	Cell Value	>=0.1459	text	NO
D222	Cell Value	between 0.125 and 0.1458	text	NO
D222	Cell Value	between 0.08333333333 and 0.12495	text	NO
D225:D247	Cell Value	top 1 values	text	NO
D225:D247	Cell Value	top 2 values	text	NO
D225:D247	Cell Value	top 3 values	text	NO
A225:A247	Cell Value	top 1 values	text	NO
A225:A247	Cell Value	top 2 values	text	NO
A225:A247	Cell Value	top 3 values	text	NO
D35,D27,D29,D37,D117,D223,D7,D20,D31,D33,D40:D41,D43,D47,D49,D58,D64,D66,D74,D76,D83,D96,D121,D131,D134,D157,D161,D189,D201	Cell Value	>=0.1459	text	NO
D35,D27,D29,D37,D117,D223,D7,D20,D31,D33,D40:D41,D43,D47,D49,D58,D64,D66,D74,D76,D83,D96,D121,D131,D134,D157,D161,D189,D201	Cell Value	between 0.125 and 0.1458	text	NO
D35,D27,D29,D37,D117,D223,D7,D20,D31,D33,D40:D41,D43,D47,D49,D58,D64,D66,D74,D76,D83,D96,D121,D131,D134,D157,D161,D189,D201	Cell Value	between 0.08333333333 and 0.12495	text	NO
A132:A133,A75,A65,A48,A32,A190:A200,A202:A221,A162:A188,A158:A160,A135:A156,A122:A130,A97:A120,A84:A95,A77:A82,A67:A73,A59:A63,A50:A57,A44:A46,A41:A42,A34:A39,A21:A30,A8:A19,A2:A6	Cell Value	=1	text	YES
A132:A133,A75,A65,A48,A32,A190:A200,A202:A221,A162:A188,A158:A160,A135:A156,A122:A130,A97:A120,A84:A95,A77:A82,A67:A73,A59:A63,A50:A57,A44:A46,A41:A42,A34:A39,A21:A30,A8:A19,A2:A6	Cell Value	=2	text	YES
A132:A133,A75,A65,A48,A32,A190:A200,A202:A221,A162:A188,A158:A160,A135:A156,A122:A130,A97:A120,A84:A95,A77:A82,A67:A73,A59:A63,A50:A57,A44:A46,A41:A42,A34:A39,A21:A30,A8:A19,A2:A6	Cell Value	between 3 and 10	text	YES

Cells with Data Validation
Cell	Allow	Criteria
C224	Any value
F224	Any value
F225:F247	Whole number	=454
C225:C246	Whole number	=454
D225:D247	Whole number	=123
C247	Whole number	=454
A225:A239	Whole number	=3364
A240	Whole number	=3364
A241:A245	Whole number	=3364
A246	Whole number	=12345
A247	Whole number	=12345
A223	Whole number	=12345
B223	Whole number	=546

 

[Michael M]

AND
why are you not using the most recent code....???

Sub MM41()
Dim r As Long, lr As Long, n As Integer, fr As Long, X As Integer
lr = Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Range("A" & lr).CurrentRegion
fr = rng.Row + 1
n = Evaluate("=MAX(IF(C" & fr & ":C" & lr - 1 & "<C" & lr & ",C" & fr & ":C" & lr - 1 & ",""""))")
X = Evaluate("=MAX(IF(F" & fr & ":F" & lr - 1 & "<F" & lr & ",F" & fr & ":F" & lr - 1 & ",""""))")
For r = lr - 1 To fr Step -1
If n = Cells(r, 3) Then
    MsgBox "You've just surpassed the number of runs <= 3 hrs for " & Cells(r, 3).Offset(0, -1).Value
    n = 100
    End If
If X = Cells(r, 6) Then
    MsgBox "You've just surpassed the number of runs > 3hrs for " & Cells(r, 6).Offset(0, -1).Value
    X = 100
End If
Next r
End Sub

 

[Ironman]

Oh heavens, I don't know how that happened - I'm changing it immediately, sorry once again.

 

[Michael M]

AND...AND...I see you have moved your "Total runs" line

198	<<< TOTAL No. IRON MAN RUNS 1984-2021

up to row 223....which will adversely impact on your "currentRegion"

IS it Bedtime ??

 

[Ironman]

LOL it's 3:15am, I have to keep up with you Aussies somehow

I promise I won't change anything else but I will need to insert rows above Row 223 for new runs and below Row 247 next year onwards - I hope that's OK?

 

[Michael M]

but I will need to insert rows above Row 223 for new runs and below Row 247 next year onwards - I hope that's OK?
Yep....but they need to be ABOVE row 223, not including row 223...keep 223 blank!
AND
If you are adding rows after 247 as long as they are relative to that block of data, that's fine.