Automatic Macro when Cell Value changes

Steven1985

Board Regular
Joined
Mar 2, 2011
Messages
66
Hi, Im new to this and also a novice with excel and would really appreciate help with my query please?

I have created a spreadsheet for work to monitor supplier performance and referrals of accounts to other areas of the business. This spreadsheet is a template to copied daily for the analysts to work.

I have in Cell B2 the date of the Alert. In cell AE2 I have the formula -today() to calculate todays date. In cell AF2 i have in the cell to monitor the Service Levels (the difference in days from Cell B2 - AE2) and the formula used in AF2 is =DATEDIF(B2, AE2, "D"). So far all this works.

What I need to do is have a macro that will run automatically when cell AF2 reaches 3 days SLA to then send an e-mail to an Analyst to promt them to chase up the referral.

Can someone please help with this? Any further information required im happy to provide. Many Thanks. Steve
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Not Application.Intersect(Range("B2"), Target) Is Nothing Then If IsNumeric(Target.Value) And Target.Value > 3 Then Call SLA</PRE>
'Working in Office 2000-2010 Dim OutApp As Object Dim OutMail As Object Dim strbody As String Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0) strbody = "Hi" & vbNewLine & vbNewLine & _ "Cell AF2 is changed" & vbNewLine & _ </PRE>
On Error Resume Next With OutMail .To = steven@work.com .CC = "" .BCC = "" .Subject = "SLA referral" .Body = strbody 'You can add a file like this '.Attachments.Add ("alert spreadsheet") .Display 'or use .Send End With On Error GoTo 0 Set OutMail = Nothing Set OutApp = NothingEnd Sub</PRE>
 
Upvote 0
That would only work if you changed the cell manually. As AF2 contains a formula you would need to use the Worksheet_Calculate event, something like

Code:
Private Sub Worksheet_Calculate()
If Range("AF2").Value >= 3 Then Call SLA
End Sub

However, this will fire off an e-mail every time the sheet calculates and AF2 is >=3. Is there a spare column (AG?) that we can use to mark that an e-mail has been sent?
 
Upvote 0
Thats a good idea, I had thought of that. I will add in AG to confirm. Great suggestion thanks.

So any idea on how I can complete the full macro? Does the majority of it seem ok just needs tweeking?
 
Upvote 0
Try this: right click the sheet tab, select View Code and paste in

Code:
Private Sub Worksheet_Calculate()
If Range("AF2").Value >= 3 And Range("AG2").Value = "" Then
    Range("AG2").Value = "X"
    Call SLA
End If
End Sub
 
Upvote 0
Sorry for all the questions. Do I past the text above then underneath add the e-mail macro that was already written?
 
Upvote 0
The e-mail macro has to go into a regular module. After you've pasted the above code in, whilst still in the code window, select Module from the Insert menu then paste in the e-mail code.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

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