If statement..run macro

[Mr.G]

I'm having problems running a "if" statement for a cell.Well I don't even know if I'm doing it correctly. Here is what I would like to do.......

If cell A3 is = or < than a 1000 run macro mail. And it should notify me. Also the cell changes colour with conditional formatting so when I need to update I'll just look for the coloured cells.

Public sub()
'on the sheet
If Range(A3) <= 1000 Then
Call Mail
End sub

Then the Macro:

Public Sub Mail()
Dim myOutlook As Object
Dim myMailItem As Object
Set myOutlook = CreateObject("Outlook.Application")
Set myMailItem = myOutlook.createitem(0)
myMailItem.Recipients.Add "B@yahoo.com"
myMailItem.Recipients.Add "A@yahoo.com"
myMailItem.Subject = "Update"
myMailItem.body = "Please update"
myMailItem.send
Set myOutlook = Nothing
End Sub


Your help would be appreciated.
Thank you

[Bob Phillips]

Public Sub mySub()
'on the sheet
If Range("A3") <= 1000 Then
Call Mail
End Sub

[Mr.G]

Doesn't run for some unkown reason......If I play the step it works but when in the workbook it doesn't run.
Any suggestion?

[lucas]

What do you mean when in the workbook it doesn't run...how are you calling it? If you want it to run automatically you will have to put it in a worksheet change event or somenthing.

[mikerickson]

I don't beleive that you can use a spreadsheet formula to call a macro.
You might use an OnTime event to check A3 every so often.

OR

How does A3 change value? As Lucas mentioned, a Worksheet_Change event could monitor A3 if it is populated in a way that triggers that event.

[johnske]

You have a muli-line If statement without an End If. Either put an End If or make it a one-line If statement


Option Explicit

Public Sub mySub()
'on the sheet
If Range("A3") <= 1000 Then
Call Mail
End If
End Sub

OR

Option Explicit

Public Sub mySub()
'on the sheet
If Range("A3") <= 1000 Then Call Mail
End Sub