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

Question

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

[Interesting Read](https://stackoverflow.com/questions/13860894/why-ms-excel-crashes-and-closes-during-worksheet-change-sub-procedure/13861640#13861640) — Siddharth Rout, Dec 13 '19 at 04:27

score 1 · Accepted Answer · answered Dec 13 '19 at 04:14

1

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

answered Dec 13 '19 at 04:14

Mathieu Guindon

69,817
8
107
235

It works for my needs. I appreciate your help and for expanding my knowledge. Kudos to you and to all those who suggested as well. – Synod Dec 13 '19 at 23:22

score 0 · Answer 2 · answered Dec 13 '19 at 03:59

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

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

2 Answers2