Run macro when you manual change a cell

To run a macro automatic when you manual change a specific cell you can use the Change event in a
worksheet module.The example on this page use Cell A1 and will run the macro if the cell value >200.

1) Right click on a sheet tab and choose view code
2) Paste the event below in the sheet module.
3) Alt-q to go back to Excel

Note: Change YourMacroName to the name of your macro in the code.
If you want the code to work for another cell or more cells you can change the range in the event.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Application.Intersect(Range("A1"), Target) Is Nothing Then
        If IsNumeric(Target.Value) And Target.Value > 200 Then
            Call YourMacroName
        End If
    End If
End Sub

Example macro

Test this example macro to create/display a Outlook mail with a small text message.
You must copy this macro in a standard module and not in the worksheet module, see
http://www.rondebruin.nl/code.htm

Note: I use .Display in the code to display the mail, you can change that to .Send

Do not forget to change Call YourMacroName to Call Mail_small_Text_Outlook in the Change event

Sub Mail_small_Text_Outlook()
'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 there" & vbNewLine & vbNewLine & _
              "Cell A1 is changed" & vbNewLine & _
              "This is line 2" & vbNewLine & _
              "This is line 3" & vbNewLine & _
              "This is line 4"

    On Error Resume Next
    With OutMail
        .To = "ron@debruin.nl"
        .CC = ""
        .BCC = ""
        .Subject = "This is the Subject line"
        .Body = strbody
        'You can add a file like this
        '.Attachments.Add ("C:\test.txt")
        .Display   'or use .Send
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

If you don't like the Outlook security warning when you use .Send instead of .Display see this page
http://www.rondebruin.nl/mail/prevent.htm

There are many more mail code examples that you can use on my site:
http://www.rondebruin.nl/sendmail.htm



Run a macro automatic when a specific formula cell reaches a certain value

This is not so easy and can be tricky. You can download a example workbook here with
two examples that both use the Worksheet_Calculate event :

1: check the value of one formula cell
2: check the value of more then one formula cell

In the examples we create a Outlook mail if the value of the formula(s) >200

Download Example workbook