Auto Run Macros when cell range is changed

[dosborn]

I am looking for a way to run my macro automatically when a specific range of cells has any input change.

If cell range S11:S34 on Sheet1 has any changes I would like to run my PartBalance macro which affects Sheet2

I have tried using the following:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$S$11:$S$34" Then
Call PartBalance
End If
End Sub

I think I have something wrong because it will not run on it's own when the cell value changes. Please help. Thanks.

 

[jim may]

You need to use the Application.Intersect method..

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("$S$11:$S$34")) Is Nothing Then
    Exit Sub
Else
    Call PartBalance
End If
End Sub

 

[dosborn]

Thank you Jim, I tried the code; however, my macro is still not running automatically. Here are some more specifics I should have probably initially included.

I have two sheets:
Sheet1 ("PART REQUEST")
Sheet2 ("PART REQUISITION")

When an input is made onto Sheet1 cell D11 and K11 if there is a remaining balance in S11 the value entered in S11 needs to fill into Sheet2 cell C11.

Do I need to do the Application.Intersect method on Sheet 2 to make it work?

Thanks for the help.