Hello Excellers, time for some #macromondays fun again. Today I want to share with you a really small piece of code, that will alert the user when a cell value reaches or exceeds a it’s target value.
If you want to see all of my blog posts in the Macro Mondays series you can find them all in the link below. Why not book mark it?, Yes it is updated EVERY Monday.
You can also download the corresponding example workbook that contains the Macro code by clicking on the download button below.
How To Excel At Excel – Macro Mondays Blog Posts.
[wpdm_package id=’10338′]
Back to some coding and Macros, to how to set up an alert, which tells the user that a call value has reached or exceeded a target.
What Does The Macro Do?
This Macro will alert the user by displaying a message box when a specified range (in this case a cell) is equal to or exceeds a value (target) specified in another cell in the worksheet. It uses the Worksheet_Change procedure which is triggered when a cell value is changed by users or by other VBA code.
How Does It Work?
FIRST. Open Visual Basic – by hitting ALT +F11 or Developer Tab – Visual Basic.
Step 1. Double click to open the worksheet that you want this code to run on, The Worksheet_Change procedure is an event that should be installed in the appropriate worksheet not in a module. Select from the left drop down Menu Worksheet the Change from the right hand drop down Menu. Excel will automatically provide the framework of the coding for you.
Step 2. This is where we set the range of N7 to be compared to the range O7, if the range N7 is greater or equal to the value inO7 the the next part of the code is trigged.
Step 3. The previous line of code triggers the Message Box if the condition we specified is met, and it displays the message we have coded into the procedure. In this case it is “Target Met”
Step 4. The code ends
That’s it, a quick and easy way to alert a user when a cell changes (in this example we specified if a target has been met).
Want T0 Copy The Code?
Private Sub Worksheet_Change(ByVal Target As Range)
If Range(“N7”).Value >= Range(“O7”) Then
MsgBox “Target Met”
End If
End Sub
