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]

F8...

 

[Ironman]

Btw I just amended the formula in B223 (and changed it back to how it was) and I got the 2 msgboxes - I thought the row above the blank row was out of the "CurrentRegion" range?

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

Is this maybe why the msgboxes are showing incorrect results?

 

[Michael M]

The reason you don't see the variable values when you hover over them is because you probably don't have "Auto Data Tips" turned on.
In the VBA window...Tools>>Options>>Editor tab>>tick all boxes

 

[Ironman]

Hi Michael - thanks, I've just done as you've suggested and that box is already checked, although 'require variable declaration' isn't checked.

If as you say, there's no issue with the code, I don't understand why the msgboxes don't make logical sense!

 

[Michael M]

In the VBA window...Tools>>Options>>Editor tab>>tick all boxes

 

[Ironman]

Ok, done.

 

[Ironman]

Hi Michael, doing the above didn't make any difference to what I was able to see.

I opened a blank workbook and copied the data into the same rows, with no data in the cells above and it still returned the same incorrect messages I received before (i.e. changed value of 24 in cell A241 to 1 and still returned "surpassed number of < 3hrs for 2007", and "runs > 3hrs for 2004" when neither have been exceeded), so it must be selecting the same incorrect cells on your sheet?

Thanks again!

 

[bobsan42]

Morning Michael!

Yes and yes, I get the row highlighting, but when I "hover the mouse over the n=" with the window focused on the code, I don't see anything?

Even if you don't see variable values as tips you can always check the value and status of a variable at runtime in the Locals Window or using the Immediate window.

 

[Ironman]

Ah thanks Bob, I'll try that. Everything in this area is new to me.

 

[Michael M]

Try using....

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