CODEWITHSUNDEEP
excelfor-loopvba
jcv
jcv

Reputation: 561

Excel VBA - "for loop" for unconnected cells

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.

Upvotes: 1

Views: 314

Answers (2)

Santosh
Santosh

Reputation: 12353

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

Upvotes: 2

ThomasMcLeod
ThomasMcLeod

Reputation: 7769

You can use the Worksheet_Change event to capture the edits. See http://msdn.microsoft.com/en-us/library/office/ff839775.aspx.

The event body receives a Range object that represents the modified cells. You can then use Application.Intersect to determine if one of your target cells is in the modified range.

Upvotes: 0

Related Questions