How to auto run a sub

[BMD]

I have this sub function and it runs correctly but I would like it to auto run each time there is a change in the range AR4:AR17.

Sub CalculateDifference()

I thought I could just change it to Sub Worksheet_Change (CalculateDifference())

Any pointers?
Thanks,
BMD

 

[VoG]

When do you want to run it?

 

[Rasm]

Try this

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim MyString() As String
    MyString = Split(Target.Address, "$")
    If MyString(0) = "AR" And Target.Row >= 4 And Target.Row <= 17 Then Call CalculateDifference
End Sub

 

[Rasm]

or this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyString() As String
MyString = Split(Target.Address, "$")
If MyString(0) = "AR" And Target.Row >= 4 And Target.Row <= 17 Then Call CalculateDifference
End Sub

 

[BMD]

THANKS,
I'm doing the Private Sub Worksheet_Change(ByVal Target As Range) but I only want it to run if I change a add or change a value in the range A4:AR15 and I also have a MsgBox and want the results to go into F1.

Thanks for all the quick replies.
BMD

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A4:AR15")) Is Nothing Then Exit Sub
Call CalculateDifference
End Sub

 

[BMD]

Thanks Peter it is working. Now I just need to convert the MsgBox to place the results in cell F1 and I would like it to run on change or if I change the background color.
Thanks, Again,
BMD

 

[VoG]

You'll need to post your CalculateDifference code.

There is no event that triggers on a change of color so you may need to rethink this.

 

[BMD]

The MsgBox I just want to have displayed in F1. So on change will not run when the color is changed? There is no on color change, then I will need to rethink the use of a button to run the CalculateDifferent function. Thanks for all the help!!!!!!!!!!!!!!

Sub CalculateDifference()

Dim Sum1 As Double
Dim Sum2 As Double
Dim c As Range
Sum1 = Application.WorksheetFunction.Sum(Range("I4:I15"))

'Red =3 Green = 10
For Each c In Range("C4:H15")
If c.Interior.ColorIndex = 3 _
Or c.Interior.ColorIndex = 10 Then
Sum2 = Sum2 + c
End If
Next c
MsgBox Sum1 - Sum2
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C4:H15")) Is Nothing Then Exit Sub
Call CalculateDifference
End Sub

 

[VoG]

Try

Sub CalculateDifference()

Dim Sum1 As Double
Dim Sum2 As Double
Dim c As Range
Sum1 = Application.WorksheetFunction.Sum(Range("I4:I15"))

'Red =3 Green = 10
For Each c In Range("C4:H15")
    If c.Interior.ColorIndex = 3 _
    Or c.Interior.ColorIndex = 10 Then
    Sum2 = Sum2 + c
End If
Next c
Range("F1").Value = Sum1 - Sum2
End Sub