Excel VBA - "for loop" for unconnected cells

Question

My purpose is to run a macro automatically on some 20 cells across my active worksheet whenever these are edited. Instead of having the same macro in place for every cell individually (makes the code very long and clumsy), I want to create a for loop which goes something like this:

for i="A10","A21","C3" ... etc
if target.address = "i" then
'execute macro
end if

I'm not quite sure how to do this... maybe another way would be a better option?

I'd really appreciate your help in the matter - thank you very much indeed.

I think You will find my other answer very useful in your case — user2140173

Santosh Santosh · Accepted Answer · 2013-06-05T09:19:00

You can use the Worksheet_Change event. Below is sample code. You need to put the code on the sheet code section

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

Dim rng As Range
Set rng = Range("A1:B5")
' If there is change in this range

    If Not Intersect(rng, Target) Is Nothing Then
        MsgBox Target.Address & " range is edited"
        ' you can do manipulation here
    End If

    Application.EnableEvents = True

End Sub

Excel VBA - "for loop" for unconnected cells

2 Answers