Range1 = Range2 and Range2 = Range1

Hi,

I have two ranges of equal size which both have the same data validation applied to them. The data validation applied is a list of R,A,G,N/A.

I would like to know if there is any coding available that would allow any cell in range1 equal the corresponding cell in range2 (i.e if the ranges are in A1:A4 and B1:B4 then A1 = B1, A2 = B2 etc..) but also so if i change the value in range2, it will also change the value in range1.

I have seen an example on this forum of how to do this with individual cells but none for named ranges. Might not be looking hard enough!

Many thanks if anyone can help.

that would be a circular reference, and i dont think it is really possible without a teritary reprisentative...

I'm awre of circular references but thought this might be possible with some cleverly worded case or if statements. I see the tertiary representitive here being the validation list as that is the item that the other two ranges have in common.

Below is a link to the post where it has been solved for individual cells:

http://www.excelforum.com/excel-gene...ll2-cell1.html

I just cant seem to map this solution over to an entire range.

Hi Holgar,

I am using a list for data/validation here because i thought it might simplfy things. I am also trying to tackle the problem by declaring cell values of the two main ranges as variants, declaring the three ranges and trying to use if statements through a "for each a in t" fashion. No luck yet though!

Range1 equal Range2.xls

Thanks to both of you for the quick responses! I have tried both codes and when I attempt to put my list names into them both codes do not seem to do anything.

I've attached a spreadsheet with both solutions contained on separate sheets. If you guys could have a look and see what i did wrong then I would really appreciate it!

Haha! Got it to work now! Thanks Holgar, very much appreciated!

Getting better all the time with tips like these, Thanks to you both.

I forgot to ask another quick thing! What if the ranges are arranged in the same column one below another? Would that have a significant impact on the code at all?

In your example where you put both solutions in one file, you used the same range names on both sheets. I didn't spend much time to figure it out (because the solution I posted was working) but that may be part of the problem. Here is the solution I posted above, saved for 2003. [keep two ranges in sync.xls]

Just tried it out my end and works too. What you said about both sheets using the same named range was probably correct thinking about it, guessing vba got a bit confused that two different codes were being applied to the same named lists!

As far as I'm concerned this thread is definitely solved for any permutation that I would like to use this code for so thank you very much to the both of you for your time!