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

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
1,069
Office Version
  1. 365
Platform
  1. Windows
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
AB
210
211136Tue 28/01/2020
212152Wed 05/02/2020
213144Sun 09/02/2020
214166Thu 13/02/2020
215156Mon 17/02/2020
216100Fri 21/02/2020
21798Tue 25/02/2020
21897Sat 29/02/2020
219126Wed 04/03/2020
220142Sun 08/03/2020
221103Thu 12/03/2020
22211TOTAL FOR 2020
223
224135Tue 22/06/2021
22557Sat 26/06/2021
226154Wed 30/06/2021
2277Sun 04/07/2021
228120Mon 12/07/2021
229124Fri 16/07/2021
23051Tue 20/07/2021
231162Sat 24/07/2021
23272Wed 28/07/2021
233101Sun 01/08/2021
2343Thu 05/08/2021
235110Mon 09/08/2021
23640Fri 13/08/2021
23777Tue 17/08/2021
23844Sat 21/08/2021
23968Wed 25/08/2021
2402Sun 29/08/2021
24189Thu 02/09/2021
2424Mon 06/09/2021
24376Fri 10/09/2021
244
24520TOTAL FOR 2021
Iron Man Log
Cell Formulas
RangeFormula
A224:A243,A211:A221A211=RANK(D211,$D$2:$D$245)
A222A222=COUNT(A211:A221)
B222B222="TOTAL FOR " & YEAR(B221)
A245A245=COUNT(A224:A244)
B245B245="TOTAL FOR " & YEAR(B243)
Named Ranges
NameRefers ToCells
Iron_Mans_2020='Iron Man Log'!$A$211:$A$221A222
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A210Cell Value=""textNO
A245Cell Value=""textNO
A244Cell Value=""textNO
A222Cell Value=""textNO
A223Cell Value=""textNO
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:A47Cell Value=1textYES
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:A47Cell Value=2textYES
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:A47Cell Valuebetween 3 and 10textYES
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:A531Cell Value=""textNO
Cells with Data Validation
CellAllowCriteria
A245Whole number=12345
A222Whole number=12345
 
But the Iron Man sheet you posted isn't using those codes !
However, just to be clear, we are still working on columns "C" & "F" as per Post #2 aren't we ??
 
Upvote 0

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Ahh OK, sorry about that.

Yes, that hasn't changed...
 
Upvote 0
Here's a bit of a pointer.
If you don't have you workbook in full screen, you can press and hold the windows button on your keyboard (usually down next to the spacebar) an at the same time press your right arrow.
This will put your code next to your workbook so you can see what is happening on the worksheetas you step through the code. you can adjust the window sizes to suit.
 
Upvote 0
It should look like this

1631421190232.png
 
Upvote 0
Thanks for that Michael.

I've just done exactly as you've suggested with the sheet and code sheet side by side. I've gone down each of the rows and amended A241 value to 1 and back to 24 for each row and keyed F8.

All I'm seeing is the row highlighted in yellow each time. I don't know what I'm supposed to be identifying (remember it's way past my bedtime :))?
 
Upvote 0
So, when you change a cell the code line highlights correct ??
Now while the code window is the active window, press F8....not "F" and 8....but the F8 button on the top of the keyboard. the yellow line should move down 1 line, and whatever action was on the previous line of code should have ben run. In the case of our data, if you hover the mouse over the "n=", it should say n=3, press F8 again and the "n=" line is executed, and if you hver over the "X=", it should say "x=8", which by my reckoning are both correct.
You can step through the entire code this way if you want to, but usually when we knw the results are correct, we simply remove the brown dot ( by clicking on it) and press F5 for the code to complete it's run !!
 
Upvote 0
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?
 
Upvote 0
Which row do you have highlighted
Is it after the n= row??
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top