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

 

[Ironman]

I've wisely decided to spend some time tidying up the above and the relevant data now looks like this:

Iron Man Log DELETE AFTER 12.09.2021.xlsm
	A	B	C	D	E	F
248	TOT	YEAR	RANK/23	No. >3HRS	YEAR	RANK/23
249	5	1984	14	0	1984	12
250	12	1986	5	2	1986	7
251	10	1987	8	1	1987	9
252	1	1988	20	0	1988	12
253	6	1989	12	0	1989	12
254	2	1990	18	0	1990	12
255	3	1991	16	0	1991	12
256	1	1993	20	0	1993	12
257	8	1994	10	5	1994	1
258	5	1996	14	0	1996	12
259	1	1998	20	0	1998	12
260	7	2000	11	1	2000	9
261	1	2001	20	0	2001	12
262	6	2002	12	0	2002	12
263	12	2003	5	5	2003	1
264	24	2004	2	5	2004	1
265	9	2005	9	2	2005	7
266	2	2006	18	0	2006	12
267	22	2007	3	4	2007	4
268	3	2008	16	1	2008	9
269	27	2019	1	4	2019	4
270	11	2020	7	0	2020	12
271	20	2021	4	4	2021	4
Iron Man Log

Cell Formulas
Range	Formula
C249:C271	C249	=RANK(A249,$A$249:$A$271)
F249:F271	F249	=RANK(D249,$D$249:$D$271)

Cells with Data Validation
Cell	Allow	Criteria
A270	Whole number	=12345
A271	Whole number	=12345

I need 2 message boxes

1) When the value in C271 changes, it should say "You've just surpassed the number of runs for (Column B cell value that has just been outranked)!
(VB new line) Rank increased to (value in C271)"
2) When the value in F271 changes, it should say "You've just surpassed the number of 3 hour runs for (Column E cell value that has just been outranked)!
(VB new line) Rank increased to (value in F271)"

Many thanks!

 

[Michael M]

So, just guessing here, as you haven't provided what that value may be....AND what about ties in the ranking ???
1) When the value in C271 changes, it should say "You've just surpassed the number of runs for (Column B cell value that has just been outranked)!
(VB new line) Rank increased to (value in C271)".....the answer for this one will be B267 ??
2) When the value in F271 changes, it should say "You've just surpassed the number of 3 hour runs for (Column E cell value that has just been outranked)!
(VB new line) Rank increased to (value in F271)".....the answer for this one will be E264....??

 

[Ironman]

Hi Michael, apologies for being unclear.

Good question once again - in that case, say "Rank increased to x (joint)"?

1. Yes, it will be B267 (previously ranked 3rd)

2. Yes, it will be E264

As a side point, you probably surmised that A249:270 and D249:270 are values for past years so they will never change.

Thanks again!

 

[Michael M]

Maaybe this for question 1....you can do the rest !!

Sub MM1()
Dim r As Long, lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
For r = lr - 1 To 249 Step -1
If Cells(r, 3) = Cells(lr, 3) - 1 Then
    MsgBox "You've just surpassed the number of runs for " & Cells(r, 3).Offset(0, -1).Value
    Exit For
End If
Next
End Sub

 

[Ironman]

Many thanks Michael, just a couple of things -

1. Should this run as a worksheet_change event? If so, will this work:

Private Sub Worksheet_Change(ByVal target As Range)
Dim r As Long, lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
For r = lr - 1 To 249 Step -1
If Cells(r, 3) = Cells(lr, 3) - 1 Then
    MsgBox "You've just surpassed the number of runs for " & Cells(r, 3).Offset(0, -1).Value
    Exit For
End If
Next
End Sub

2. You've specified row 249 - this number will change with more rows being added above. If I used a named range instead (say "ABC") to overcome this, how would the code have to look?

3. As I haven't run the code yet, I'm guessing it will only work for Q1 and I'm adapting it for Q2?

Thanks again!

 

[Michael M]

I still think there may be issues with this code...haven't tested as yet !

1. worksheet_change
2. You've specified row 249 - won't you be adding extra dat at row 271 onwards ..are you going back beyond 1984 ?

3. As I haven't run the code yet, I'm guessing it will only work for Q1 and I'm adapting it for Q2?....Yes !

 

[Ironman]

1. Noted, thanks
2. Yes, but as data similar to the below is being inserted above row 249 then that row number will no longer be correct in another 3 days

4	Mon 06/09/2021	15.1	3:46:24	15:00
76	Fri 10/09/2021	10.6	2:32:42	14:24

 

[Michael M]

So how do we know where the first row of this table will be...that seems a bit backward to me....always thought the latest would be added after row 271
I think this one is more accurate

Sub MM3()
Dim r As Long, lr As Long, n As Integer
lr = Cells(Rows.Count, "A").End(xlUp).Row
n = Evaluate("=MAX(IF(C249:C" & lr - 1 & "<C" & lr & ",C249:C" & lr - 1 & ",""""))")
For r = lr - 1 To 249 Step -1
If n = Cells(r, 3) Then
    MsgBox "You've just surpassed the number of runs for " & Cells(r, 3).Offset(0, -1).Value
    Exit For
End If
Next
End Sub

 

[Ironman]

I guess you're right Michael, but the entries above the table are in chronological order, going back to 1984 - I don't know where else the table could go, other than on a new sheet.

Maybe if I relocated it at the top, to the right of the data, would that be better?