Automatic Macro when Cell Value changes

[Steven1985]

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

 

[VoG]

Hi. See if this helps http://www.rondebruin.nl/mail/change.htm

 

[Steven1985]

Hi VoG, I have tried using this but im going wrong somewhere as it doesnt appear to work.

 

[VoG]

Can you post the code that you have tried.

 

[Steven1985]

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>

 

[VoG]

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

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?

 

[Steven1985]

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?

 

[VoG]

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Calculate()
If Range("AF2").Value >= 3 And Range("AG2").Value = "" Then
    Range("AG2").Value = "X"
    Call SLA
End If
End Sub

 

[Steven1985]

Sorry for all the questions. Do I past the text above then underneath add the e-mail macro that was already written?

 

[VoG]

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.