Closed Thread
Results 1 to 22 of 22

Cell1 = Cell2 and Cell2 = Cell1

  1. #1
    Registered User
    Join Date
    06-09-2009
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2007
    Posts
    17

    Cell1 = Cell2 and Cell2 = Cell1

    Is there a way to set 2 cells equal, while allowing either one to be manually changed? So if I put in value of 10 for Cell1, Cell1 and and Cell2 will both be equal to 10. Then if I change Cell2 to a value of 20, both cells will be equal to 20.

    Thank you to anyone who can help.
    Last edited by dej222; 06-09-2009 at 08:30 PM.

  2. #2
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    Re: Cell1 = Cell2 and Cell2 = Cell1

    Hello Dej222, and welcome to the forum.

    You could do this using a macro in the worksheets Change event, like so:
    Please Login or Register  to view this content.
    To add this code to your worksheet, right-click on the worksheet tab and choose View Code. Copy and paste the code shown above into that window and then close the VB Editor window. From that point forward, whenever you change cell A1 or A2, the other will update along with.

    Change A1 and A2 in the code to your Cell1 and Cell2, of course.

    Hope that helps!

  3. #3
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Cell1 = Cell2 and Cell2 = Cell1

    slightly different code
    Please Login or Register  to view this content.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Registered User
    Join Date
    06-09-2009
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: Cell1 = Cell2 and Cell2 = Cell1

    Paul,

    I am a VBA novice but I believe this would only work if the cells are in the same worksheet, correct? If so, is there a way to do the same thing for cells in different worksheets?

    Thanks, I appreciate the help.

  5. #5
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    Re: Cell1 = Cell2 and Cell2 = Cell1

    Hi again,

    You could do the same thing, but you'd have to have a Worksheet_Change event setup in each worksheet. For example, in Sheet1 you could have:
    Please Login or Register  to view this content.
    and in Sheet2 you'd have code like:
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    06-09-2009
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: Cell1 = Cell2 and Cell2 = Cell1

    Thank you Paul and Martin! Problem solved...

  7. #7
    Registered User
    Join Date
    06-21-2012
    Location
    Kansas
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Cell1 = Cell2 and Cell2 = Cell1

    I hope it's OK to necro this thread, but I have a followup question regarding the exact same original post.

    I used the code above to successfully do what dej222 was requesting on two open cells in my spreadsheet.
    However, I have a problem when I move it to cells in my actual application.

    Problem:
    The cells I'm wanting to make equal to each other use a validation list for data entry. When I use either of the codes above I get the following error when I select a value for the cell from the drop down list:

    Run-time error '-2147417848 (80010108)
    Method 'Value' of object 'Range' failed.



    Any advice on how to avoid this error?

    Thank you,
    Mark

    EDIT: Added example file with working cells (Column C) and non-working cells (Column A - validation list cells)
    Attached Files Attached Files
    Last edited by mthemanj; 06-21-2012 at 04:05 PM.

  8. #8
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Cell1 = Cell2 and Cell2 = Cell1

    Hi Mark & Welcome to the Forum, unfortunately:

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
    HTH
    Regards, Jeff

  9. #9
    Registered User
    Join Date
    10-04-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    19

    Re: Cell1 = Cell2 and Cell2 = Cell1

    I'm having the same problem but with two named ranges of equal size! Do you guys have any idea how to tackle this problem with ranges instead of individual cells?

  10. #10
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Cell1 = Cell2 and Cell2 = Cell1

    rawcode,

    Welcome to the Forum, unfortunately:

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  11. #11
    Registered User
    Join Date
    10-04-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    19

    Re: Cell1 = Cell2 and Cell2 = Cell1

    Ok, I will post this question in a new thread although I thought it was relevant seeing as it's jsut expanding upon the solution suggested in this thread.

  12. #12
    Registered User
    Join Date
    10-17-2013
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Cell1 = Cell2 and Cell2 = Cell1

    I agree with rawcode and am not searching for his post. his question was valid here and should be answered here to help members find answers quickly instead of going to another post that is really just a continuation of the same topic.

  13. #13
    Registered User
    Join Date
    01-16-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Cell1 = Cell2 and Cell2 = Cell1

    I tried this code but it didn't work between the two sheets. Is there something I'm missing?


    Quote Originally Posted by Paul View Post
    Hi again,

    You could do the same thing, but you'd have to have a Worksheet_Change event setup in each worksheet. For example, in Sheet1 you could have:
    Please Login or Register  to view this content.
    and in Sheet2 you'd have code like:
    Please Login or Register  to view this content.

  14. #14
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Cell1 = Cell2 and Cell2 = Cell1

    akraintz2014 welcome to the forum

    In case you missed post 8 and 10...

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  15. #15
    Registered User
    Join Date
    01-16-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Cell1 = Cell2 and Cell2 = Cell1

    I am discussing the EXACT same thing as what was just said. I had issues with their code, so I am replying to discuss the same code that was in this post.

    Quote Originally Posted by FDibbins View Post
    akraintz2014 welcome to the forum

    In case you missed post 8 and 10...

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

  16. #16
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Cell1 = Cell2 and Cell2 = Cell1

    This is dej222's thread (and is anyway over 4 1/2 years old).

    It is against forum rule 2 to ask your own questions on another member's thread. Please start your own thread, and reference this 1 if you feel it is relevant

  17. #17
    Registered User
    Join Date
    12-08-2017
    Location
    Cordoba, ARgentina
    MS-Off Ver
    Windows 10 x64
    Posts
    1

    Re: Cell1 = Cell2 and Cell2 = Cell1

    Quote Originally Posted by Paul View Post
    Hi again,

    You could do the same thing, but you'd have to have a Worksheet_Change event setup in each worksheet. For example, in Sheet1 you could have:
    Please Login or Register  to view this content.
    and in Sheet2 you'd have code like:
    Please Login or Register  to view this content.
    After using this code every time I change values in any of those cells Excel stops working and restarts. Afterwards I can see the code worked. But only after restart. Do any of you had the same issue?

  18. #18
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Cell1 = Cell2 and Cell2 = Cell1

    Dr.Kreshel,
    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    Ben Van Johnson

  19. #19
    Registered User
    Join Date
    01-15-2020
    Location
    Brussels
    MS-Off Ver
    2016
    Posts
    3

    Re: Cell1 = Cell2 and Cell2 = Cell1

    I am reading this late but it can help.
    The code generates an infinite loop between the two cells. So the excel file stops working, because it will be indefinitely working on the change A1-A2 then A2-A1 then A1-A2....
    I don't have a solution to this but the code won't work. Needs modification
    Thanks

  20. #20
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    Re: Cell1 = Cell2 and Cell2 = Cell1

    Perhaps check into the Application.EnableEvents property...

  21. #21
    Registered User
    Join Date
    03-29-2021
    Location
    Houston, Texas
    MS-Off Ver
    16.43
    Posts
    1

    Re: Cell1 = Cell2 and Cell2 = Cell1

    Does Anyone know the code for this but regarding 4 cells that should all change together as inputs?

  22. #22
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,366

    Re: Cell1 = Cell2 and Cell2 = Cell1

    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1