Macro OverDrive "XXXXX to 5 cells together"

[4 Barrel Harold]

Mission Impossible,
For me that is LOL, Hey MrExcel Guys and Gals, this is what I'm trying to do and I'm not sure what it called or what I need to do to accomplish this task. On my worksheet I have 5 cells (A1, C105, G55, R21, & Z21) and what I would like to happen is if I enter data in any random cell R21 of the 5 cells listed above the data update the other 4 cells, and vise versa if choose any of the other 4 Cells and enter new data. Any Help Greatly Appreciated Thanks

 

[gallen]

Hello,

So if a user changes any of the mentioned cells you want each of those cells to read the same?

The code here does that. Apologies if I've misunderstood

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim sAddress As String 'address of range of cells
    
    sAddress = "A1,C105,G55,R21,Z21"
    
    'check if the cell that has changed is one of our cells in 'sAddress'
    If Not Intersect(Range(sAddress), Target) Is Nothing Then
        Application.EnableEvents = False
            Range(sAddress) = Target
        Application.EnableEvents = True
    End If
End Sub

 

[4 Barrel Harold]

gallen, Hey the code you provided works like a charm. I will be using this one quite a bit. Thanks a Million

 

[4 Barrel Harold]

gallen the code works great, 1 more question on this, if "A1,C105,G55,R21,Z21" changes when changed how do I get another group on same sheet such as "A2,C106,G56,R22,Z22" to do the same?

 

[Rick Rothstein]

gallen the code works great, 1 more question on this, if "A1,C105,G55,R21,Z21" changes when changed how do I get another group on same sheet such as "A2,C106,G56,R22,Z22" to do the same?

Since your new question looks like the cells under the the original cells, the natural question is... are you going to want to do this to the cells under those as well? If so, how far down will you need to go with this?

 

[4 Barrel Harold]

Rick, Your right it does appear that way, here is what I should have stated in the question "the cells will be in other locations on the sheet", Thanks for pointing that out Rick

 

[Rick Rothstein]

Rick, Your right it does appear that way, here is what I should have stated in the question "the cells will be in other locations on the sheet", Thanks for pointing that out Rick

You need to tell us all of the individual ranges that this needs to be done for. You have told us "A1,C105,G55,R21,Z21"... what are the other cell groups you need this for (we need to know so we can code the interaction between them)?

 

[4 Barrel Harold]

Rick the new Ranges would be "H41,J25,P62,Q60,W6"

 

[4 Barrel Harold]

Ok Guys and Gals I greatly appreciate the help today, gallen started a code earlier this morning after some kicking it around a bit it's now upgraded to this code thanks again gallen this code works like I want it to but if it could be streamlined more I'M All Ears Thanks Rick for your time. 4-Barrel Harold

Private Sub Worksheet_Change(ByVal Target As Range)
    
        Dim sAddress As String 'address of range of cells
        Dim bAddress As String 'address of range of cells
    
            sAddress = "A1,C15,G5,R2,Z14"   Cells used as examples.
            bAddress = "A4,C19,G9,R6,Z18"   Cells used as examples.
    
            'check if the cell that has changed is one of our cells in 'sAddress'
            If Not Intersect(Range(sAddress), Target) Is Nothing Then
                Application.EnableEvents = False
                    Range(sAddress) = Target
                Application.EnableEvents = True
            End If
            
            'check if the cell that has changed is one of our cells in 'sAddress'
            If Not Intersect(Range(bAddress), Target) Is Nothing Then
                Application.EnableEvents = False
                    Range(bAddress) = Target
                Application.EnableEvents = True
            End If
       
End Sub

 

[gallen]

If you have repeated code it tends to mean you can trim it down. In very large applications you may get performance benefits but in general, it just makes it much easier to read and amend.

In your insatnce I'm guessing other ranges will be added to the ones you have here. The If statements are very simple so only need one line each. As it stands, there's not many errros can appear here but whenever you disable anything like events it's always good practice to have error handling too. (In fact it's just always good practice )

No error handling here but easier to read code if you end up with several ranges:

Private Sub Worksheet_Change(ByVal Target As Range)
    
        Dim sAddress As String 'address of range of cells
        Dim bAddress As String 'address of range of cells
    
            sAddress = "A1,C15,G5,R2,Z14"   'Cells used as examples.
            bAddress = "A4,C19,G9,R6,Z18"   'Cells used as examples.
            
            Application.EnableEvents = False
            
            'check if the cell that has changed is one of our cells in 'sAddress'
            If Not Intersect(Range(sAddress), Target) Is Nothing Then Range(sAddress) = Target
            
            'check if the cell that has changed is one of our cells in 'bAddress'
            If Not Intersect(Range(bAddress), Target) Is Nothing Then Range(bAddress) = Target
            
            Application.EnableEvents = True


End Sub