How to do the impossible (almost)

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Application.Intersect(Target, Range("A1")) Is Nothing Then
   If Target.Value > 10 Then
      MsgBox "Put your macro code here in place of the MsgBox line"
   End If
End If
End Sub

When A1 is changed to a value greater than 10, the macro code will run.

To get to the Worksheet Event code, right-click the sheet tab and choose View Code.




From CPearson.com

Also see:
Change Events

Also:
Microsoft KnowledgeBase:
How to Run a Macro When Certain Cells Change

After posting this, Ross Mclean of Methodsinexcel.co.uk came up with a great work around using a User Defined Function.

Public Function RMAC(ByVal Macro_Name As String,  _

ByVal Arg1 As Variant)

RMAC = Application.Run(Macro_Name, Arg1)

End Function

You can see the simple coding here:

Running a macro from a cell.

Thanks Ross!

Keep in mind that some commands will be ignored. A macro run from the worksheet like this will not change the Excel environment.

For example (watch line wrap), this VBA code:

Public Function RMAC _
(ByVal Macro_Name As String, _
ByVal Arg1 As Variant)
RMAC = Application.Run _
(Macro_Name, Arg1)
End Function

Sub MyMacro(arg As String)
ActiveCell.Interior.ColorIndex _
= 3
Beep
End Sub

=rmac("MyMacro","yada")

Labels: Macros, VBA