VBA to create message box when cell value exceeds adjacent cell value by less than a certain amount and 2 cells move one cell to the right each year

[Ironman]

Hi

Row 369 contains years - AN369 is 2020, AO369 is 2021, AP369 is 2022 and so on, up to 2061.
Col AO372 contains a formula value for the current year, currently 2021.
Col AN372 contains a formula value for the previous year, currently 2020.

Exercise Log 2 MACRO TEST.xlsm
	AN	AO	AP
369	2020	2021	2022
372	527	524
Daily Tracking

Cell Formulas
Range	Formula
AN372:AO372	AN372	= SUMPRODUCT(--($A2:$A367<=YEP_LastDate),AN2:AN367)

Named Ranges
Name	Refers To	Cells
EntRng	=OFFSET('Daily Tracking'!$Y$2:$Y$367,0,YEAR(TODAY())-2005)	AO372

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AO372	Expression	=AO372>=LARGE($C$375:$CC$375,1)	text	YES
AO372	Expression	=AO372>=LARGE($C$375:$CC$375,2)	text	YES
AO372	Expression	=AO372>=LARGE($C$375:$CC$375,3)	text	YES
AN372	Expression	=AN372>=LARGE($C$375:$CC$375,1)	text	YES
AN372	Expression	=AN372>=LARGE($C$375:$CC$375,2)	text	YES
AN372	Expression	=AN372>=LARGE($C$375:$CC$375,3)	text	YES
B372:AE372,AP372:CC372	Expression	=B372>=LARGE($C$375:$CC$375,1)	text	YES
B372:AE372,AP372:CC372	Expression	=B372>=LARGE($C$375:$CC$375,2)	text	YES
B372:AE372,AP372:CC372	Expression	=B372>=LARGE($C$375:$CC$375,3)	text	YES

When the value in AO372 (this year) exceeds AN372 (last year) by less than 11 I need a simple message box "this year's total is greater than last year".

I also need the column to auto update each year, so on Jan 1 2022 the 2 cells compared will be AP372 (for 2022) and AO372 (for 2021) and so on each new year.

Hope you can help?

Many thanks!

 

[6StringJazzer]

When the value in AO372 (this year) exceeds AN372 (last year) by less than 11 I need a simple message box "this year's total is greater than last year".

If this year's value exceeds last year's value by 11 or more, this year's total is still greater than last year's. You do not want a message in that case?

Here is code that checks for this condition every time the sheet is calculated. It goes into the module for that sheet. It is dependent the rows and columns you have specified so if the worksheet format changes, the code must be updated accordingly.

Private Sub Worksheet_Calculate()

   Dim ThisYearCell As Range
   
   Set ThisYearCell = Range("369:369").Find(after:=Range("AM369"), what:=Year(Date), LookIn:=xlValues, lookat:=xlWhole)
   If ThisYearCell Is Nothing Then
      MsgBox "Could not find year " & Year(Date) & " in row 369"
   ElseIf ThisYearCell.Column < [AN369].Column Then
      MsgBox "Could not find year " & Year(Date) & " in row 369"
   Else
      If Cells(372, ThisYearCell.Column) - Cells(372, ThisYearCell.Column - 1) > 0 And _
         Cells(372, ThisYearCell.Column) - Cells(372, ThisYearCell.Column - 1) < 11 Then
         MsgBox "This year's total is greater than last year"
      End If
   End If

End Sub

 

[Ironman]

Hi, many thanks for helping me out.

If this year's value exceeds last year's value by 11 or more, this year's total is still greater than last year's. You do not want a message in that case?

Thank you for your suggestion - that's correct, I don't want a message when last year's value is exceeded by 11 or more, because I won't need to be reminded again.

Can I please just ask you if your code automatically advances the column by 1 each Jan 1, as in my question?

Also, is it possible you could kindly add Row 379 with the same columns into your code, where this year (AO379) exceeds last year (AN379) by <2 (I was going to adapt your code myself but I then remembered that I can't have duplicate 'dims' and so duplicating all your code with 'ThisYearCell1' would look a bit 'clunky').

Thanks again!

 

[6StringJazzer]

Thank you for your suggestion - that's correct, I don't want a message when last year's value is exceeded by 11 or more, because I won't need to be reminded again.

OK, I took you quite literally and that's what the code does.

Can I please just ask you if your code automatically advances the column by 1 each Jan 1, as in my question?

You were very specific about that, so that is what the code does.

Also, is it possible you could kindly add Row 379 with the same columns into your code, where this year (AO379) exceeds last year (AN379) by <2 (I was going to adapt your code myself but I then remembered that I can't have duplicate 'dims' and so duplicating all your code with 'ThisYearCell1' would look a bit 'clunky').

You don't need more variables, everything is determined relative to where it finds the cell with this year:

Private Sub Worksheet_Calculate()

   Dim ThisYearCell As Range
 
   Set ThisYearCell = Range("369:369").Find(after:=Range("AM369"), what:=Year(Date), LookIn:=xlValues, lookat:=xlWhole)
   If ThisYearCell Is Nothing Then
      MsgBox "Could not find year " & Year(Date) & " in row 369"
   ElseIf ThisYearCell.Column < [AN369].Column Then
      MsgBox "Could not find year " & Year(Date) & " in row 369"
   Else
    
      If Cells(372, ThisYearCell.Column) - Cells(372, ThisYearCell.Column - 1) > 0 And _
         Cells(372, ThisYearCell.Column) - Cells(372, ThisYearCell.Column - 1) < 11 Then
         MsgBox "This year's total is greater than last year (row 372)"
      End If
 
      If Cells(379, ThisYearCell.Column) - Cells(379, ThisYearCell.Column - 1) > 0 And _
         Cells(379, ThisYearCell.Column) - Cells(379, ThisYearCell.Column - 1) < 2 Then
         MsgBox "This year's total is greater than last year row (379)"
      End If
 
   End If

End Sub

 

[Ironman]

That's brilliant, thanks ever so much Jeff (sorry, I've only just noticed your name).

Best regards

Paul

 

[6StringJazzer]

Glad to help!

 

[Ironman]

Hi Jeff, I forgot to ask - I've noted it's a Worksheet_Calculate event. Will your code 'fire' only when the 2 specific cell values change as specified (which is what I want) or will it keep firing whenever any other calculation is done in the sheet?

 

[6StringJazzer]

It fires whenever any calculation is done. There is no event that can tell if the result of a formula has changed. The Worksheet_Change event detects when an entry to a cell has changed, but it is unaware when a formula result changes.

To zero in on just the two cells you are interested in would require starting from the formulas in those cells and working backwards to find out what changes can cause those cells to be updated. You have shown the formulas in those cells

= SUMPRODUCT(--($A2:$A367<=YEP_LastDate),AN2:AN367)

but then I would need to know what's in column A, and the cells in AN2:AN367. If any of them are formulas, then the tracing would have to continue from there, until we know what cell a user is typing in to change the final result.

 

[Ironman]

OK Jeff, that's fine, thanks a lot for taking the time to explain.