CODEWITHSUNDEEP
excelvba
Synod
Synod

Reputation: 29

Autorun excel vba when values in any cell in a range changes

I am new in excel VBA programming. I have the code below, and what I am hoping to achieve is that if a change in made in any of the cells within that range "E19:E23", Some_Function will run. It runs if it is assigned a single cell to the Range(). But how can i make it work so that the function/s can run if there is a change in any of the cells in any range(i.e. "E19:E23")? I appreciate any help. 

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = Range("E19:E23").Address Then 'When Amount of loan is entered

   Call Some_Function

End If

End Sub

Thanks

Upvotes: 0

Views: 70

Answers (2)

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

You're looking for Application.Intersect:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Application.Intersect(Target, Me.Range("E19:E23")) Is Nothing Then
        'there's no intersecting range
        Exit Sub
    End If

    'Target intersects E19:E23
    DoSomething

End Sub

Private Sub DoSomething()
    '...
End Sub

Upvotes: 1

braX
braX

Reputation: 11755

The Target also has a Row and a Column property, so you can make an If statement like this:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column = 5 And Target.Row >= 18 And Target.Row <= 23 Then
    MsgBox Target.Address
  End If
End Sub

Upvotes: 0

Related Questions