Multiple Cell change event trigger VBA code

[nagovind]

Dear all,
Kindly advise the resolution for multiple cell change event to trigger same VBA code

Cell A1
Cell C2
Cell D9

If any of above cell value changes need to trigger a VBA code

Thank you
Nagovind

 

[AlanSidman]

You will need a worksheet_change event to be created. You will need code that works like this:

If Target.Address =$A$1 or Target.Address = $C$2 or Target.Address = $D$9 then
Do Something

 

[Hui]

Alan's code is slightly in error

Addresses are Text and so need " around them

If Target.Address = "$A$1" Or Target.Address = "$C$2" Or Target.Address = "$D$9" Then
  MsgBox "This works"
Else
  MsgBox "This doesn't"
End If

 

[nagovind]

Thanks a lot, Hui, ...
Its suiting my requirements

Regards
Nagovind

 

[AlanSidman]

Hui,

Thanks. Oversight when writing code and not testing.

 

[Hui]

Hui,

Thanks. Oversight when writing code and not testing.

From experience I make most errors when answering on my phone and hence not having tested the response first...

 

[p45cal]

If there are more than 3 noncontiguous areas/cells to check then your IF statement can get quite cumbersome - imagine 10 cells.
Another way is along the lines of:

If Not Intersect(Target, Range("A1,C2,D9")) Is Nothing Then
  'do something
End If

You might want to assign a variable:

Set RngDetect = Range("A1,C2,D9")
If Not Intersect(Target, RngDetect) Is Nothing Then
  'do something
End If

and if you need to iterate through all the cells which have been changed (someone's done a copy/paste with multiple cells for example) then you can:

Set RngCellsToProcess = Intersect(Target, Range("A1,C2,D9"))
If Not RngCellsToProcess Is Nothing Then
  For Each cll In RngCellsToProcess.Cells
    'do something
  Next cll
End If

Another tip to get the range address of the non-contiguous range quickly is to select the noncontiguous cells on the sheet while holding the Ctrl key down, then in the Immediate pane type:
?selection.address(0,0)
and Enter. You'll get a range address you can put double quotes around for inserting in the code.

 

[nagovind]

Dear p45cal,

Thanks for your detailed reply
Regards