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]

Yes, it's the latest version AFAIK from when I'm prompted to update - I've located it in the Office toolbar and I've unchecked conditional formatting/data validation - if that options box doesn't show next time I use it I'll see if my version is out of date.

 

[Michael M]

Best way is to simply use the XL2BB icon in the replytoolbar.
That will download the lates version

 

[Ironman]

Anyway...(I promise I haven't changed anything!) I'm still getting incorrect msgboxes - # runs <3hrs for 2007 has been surpassed (it hasn't) and # runs >3hrs for 2004 (it hasn't)...

 

[Michael M]

Ok, have you tried the Bookmark / F8 method to see what the variables are ??

 

[Ironman]

Sorry, I haven't learned those terms yet - can you give me a pointer?

 

[Michael M]

Reread Post #38

 

[Ironman]

Ahh, breakpoints, OK, I'm back on it.

 

[Michael M]

Sorry, my bad....I call them bookmarks and / or Breakpoints....depending on my thought patterns on the day !!

 

[Michael M]

AND I see your not using the code from Post #46, which is the latest I provided for the "Iron Man Log" sheet !!

 

[Ironman]

Hmmm

This is what's in my sheet

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

and this is Post #46

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

I don't see any difference!