Microsoft Office Tutorials and References
In Depth Information
Running a Macro When a Cell Changes
In the code below, Union(Range("A1"), Range("B1"), Range("C1")) creates the
range of interest. Intersect(Target,Union(Range("A1"), Range("B1"),
Range("C1"))) checks to see if the current selection (Target) is in the range of
View the Appendix to learn how to store this procedure
in a Standard module.
Option Explicit¶
' * * * * *¶
Sub RunMacroOnChange(ByVal Target As Range)¶
'First, check that the range is one of those three¶
If Intersect(Target, Union(Range("A1"), Range("B1"), _¶
Range("C1"))) Is Nothing Then¶
Exit Sub¶
End If¶
'Ok, one of the cells has changed, evaluate the condition:¶
'A1 = 1 and B1 <> 5 and C1 > 10¶
If Range("A1").Value = 1 And Range("B1").Value <> 5 And _¶
Range("C1").Value > 10 Then¶
'It's true, so, call Macro1¶
End If¶
End Sub¶
' * * * * *¶
Sub Macro1()¶
MsgBox "It worked !"¶
End Sub¶
View the Appendix to learn how to store this procedure
in a Worksheet module.
Option Explicit¶
' * * * * *¶
Private Sub Worksheet_Change(ByVal Target As Range)¶
RunMacroOnChange Target¶
End Sub¶
The code in the standard module uses a condition to run the macro 'Macro1'.
This condition should be adapted to the user’s specific needs. The cells that are
involved in the condition will need to be changed as well.
Search JabSto ::

Custom Search