# 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

##### Well-known Member
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
ANAOAP
369202020212022
372527524
Daily Tracking
Cell Formulas
RangeFormula
AN372:AO372AN372= SUMPRODUCT(--(\$A2:\$A367<=YEP_LastDate),AN2:AN367)
Named Ranges
NameRefers ToCells
EntRng=OFFSET('Daily Tracking'!\$Y\$2:\$Y\$367,0,YEAR(TODAY())-2005)AO372
Cells with Conditional Formatting
CellConditionCell FormatStop If True
AO372Expression=AO372>=LARGE(\$C\$375:\$CC\$375,1)textYES
AO372Expression=AO372>=LARGE(\$C\$375:\$CC\$375,2)textYES
AO372Expression=AO372>=LARGE(\$C\$375:\$CC\$375,3)textYES
AN372Expression=AN372>=LARGE(\$C\$375:\$CC\$375,1)textYES
AN372Expression=AN372>=LARGE(\$C\$375:\$CC\$375,2)textYES
AN372Expression=AN372>=LARGE(\$C\$375:\$CC\$375,3)textYES
B372:AE372,AP372:CC372Expression=B372>=LARGE(\$C\$375:\$CC\$375,1)textYES
B372:AE372,AP372:CC372Expression=B372>=LARGE(\$C\$375:\$CC\$375,2)textYES
B372:AE372,AP372:CC372Expression=B372>=LARGE(\$C\$375:\$CC\$375,3)textYES

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!

Last edited:

### 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.
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.

VBA Code:
``````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``````

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.

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!

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.

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:
Rich (BB code):
``````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``````

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

Best regards

Paul

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?

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
Excel Formula:
``= 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.

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

1,203,052
Messages
6,053,234
Members
444,648
Latest member
sinkuan85

### 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.

### Which adblocker are you using?

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

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