loop non adjacent cells
 

I am trying to execute a macro for all the rows which a user pastes a value
into. For example, the user selects cells AJ94, AJ108, AJ120 and pastes a
copied value.
The problem is that the msgbox returns $AJ$94 3 times.
I have (excuse any mistakes I have...I cut a whole bunch of junk)
Any thoughts?

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = True
Dim DataRng, TrgtRng As String
Dim rng, c As Range
DataRng = ActiveWorkbook.Names("trgtrng").RefersToRange.Addr ess
€˜trgtrng refers to column AJ in the example
If Not Application.Intersect(Target, Range(DataRng)) Is Nothing Then
TrgtRng = Target.AddressLocal()
Set rng = Range(TrgtRng)
For Each c In trng
MsgBox ActiveCell.AddressLocal()
Next c
Endif
End Sub

loop non adjacent cells
 

Perhaps I am not understanding, but your messagebox is giving you the
ActiveCell address, and you are not changing the active cell; you are looping
through c. If you do MsgBox c.address I think you will get the results you
are looking for.

"EWR" wrote:

I am trying to execute a macro for all the rows which a user pastes a value
into. For example, the user selects cells AJ94, AJ108, AJ120 and pastes a
copied value.
The problem is that the msgbox returns $AJ$94 3 times.
I have (excuse any mistakes I have...I cut a whole bunch of junk)
Any thoughts?

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = True
Dim DataRng, TrgtRng As String
Dim rng, c As Range
DataRng = ActiveWorkbook.Names("trgtrng").RefersToRange.Addr ess
€˜trgtrng refers to column AJ in the example
If Not Application.Intersect(Target, Range(DataRng)) Is Nothing Then
TrgtRng = Target.AddressLocal()
Set rng = Range(TrgtRng)
For Each c In trng
MsgBox ActiveCell.AddressLocal()
Next c
Endif
End Sub