Link to home
Start Free TrialLog in
Avatar of Terrygordon
Terrygordon

asked on

Target.Address conflict in Excel VBA

Hi

I am trying to set up 2 ranges of cells on a worksheet so that if the user changes the value in the cells in column G, the corresponding cells in Column P are changed using a calculation in a VBA macro, and, if the user changes the values in the cells in Column P, the corresponding cells in Column G are changed by a macro.

For example, change the value in G1 and P1 changes, change the value in P1and G1 changes.

Because I don't want to use formulas in these cells (the user needs to be able to enter actual figures in either section, I have tried using target.address in worksheet_change. This works fine if it is just G1 affecting P1, but when I enter a complementary change event for P1 it throws up various errors including 'Stack overflow'. There are various other target.address references in the worksheet_change event and these all work fine - provided it is one-way traffic.

I suspect this is because there is some sort of conflict between the cells targeting each other?

So, is there another way of getting the values in the cells to change, as in the example above?

Regards

Terry
ASKER CERTIFIED SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Terrygordon
Terrygordon

ASKER

Thanks Martin - can't believe it was that simple!

Regards

Terry
You're welcome and I'm glad I was able to help.

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2016
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2016