Macro OverDrive "XXXXX to 5 cells together"

4 Barrel Harold

New Member
Joined
Jun 15, 2018
Messages
21
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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
Code:
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
 
Last edited:
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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)?
 
Upvote 0
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

Code:
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
 
Upvote 0
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:

Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,333
Members
452,636
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top