Formula Question

%3CLINGO-SUB%20id%3D%22lingo-sub-1551603%22%20slang%3D%22en-US%22%3EFormula%20Question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1551603%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20there%20a%20way%20to%20make%202%20cells%20equal%20eachother%3F%20Like%20can%20I%20have%20A1%20%3D%20A2%20and%20A2%20%3D%20A1%3F%20I%20need%20to%20be%20able%20to%20change%20the%20number%20in%20either%20one%20of%20them%20and%20have%20them%20change.%20For%20example%2C%20right%20now%20I%20can%20make%20A1%20%3D%20A2.%20So%20if%20A2%20has%20the%20number%202%2C%20then%20A1%20is%20number%202.%20But%20I%20also%20need%20to%20be%20able%20to%20change%20A1%20to%201%20and%20have%20A2%20change%20to%201.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1551603%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1551646%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1551646%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F742859%22%20target%3D%22_blank%22%3E%40emlicata%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20need%20some%20VBA%20code%20for%20this.%3C%2FP%3E%0A%3CP%3EWarning%3A%20you%20won't%20be%20able%20to%20use%20Undo%20when%20you%20change%20A1%20or%20A2.%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3ERight-click%20the%20sheet%20tab.%3C%2FLI%3E%0A%3CLI%3ESelect%20'View%20Code'%20from%20the%20context%20menu.%3C%2FLI%3E%0A%3CLI%3ECopy%20the%20code%20listed%20below%20into%20the%20worksheet%20module.%3C%2FLI%3E%0A%3CLI%3EPress%20Alt%2BF4%20to%20close%20the%20Visual%20Basic%20Editor.%3C%2FLI%3E%0A%3CLI%3ESave%20the%20workbook%20as%20a%20macro-enabled%20workbook.%3C%2FLI%3E%0A%3CLI%3EMake%20sure%20that%20you%20allow%20macros%20when%20you%20open%20the%20workbook.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%0A%20%20%20%20If%20Target.CountLarge%20%26gt%3B%201%20Then%20Exit%20Sub%0A%20%20%20%20If%20Target.Address%20%3D%20%22%24A%241%22%20Then%0A%20%20%20%20%20%20%20%20Application.EnableEvents%20%3D%20False%0A%20%20%20%20%20%20%20%20Range(%22A2%22).Value%20%3D%20Range(%22A1%22).Value%0A%20%20%20%20%20%20%20%20Application.EnableEvents%20%3D%20True%0A%20%20%20%20ElseIf%20Target.Address%20%3D%20%22%24A%242%22%20Then%0A%20%20%20%20%20%20%20%20Application.EnableEvents%20%3D%20False%0A%20%20%20%20%20%20%20%20Range(%22A1%22).Value%20%3D%20Range(%22A2%22).Value%0A%20%20%20%20%20%20%20%20Application.EnableEvents%20%3D%20True%0A%20%20%20%20End%20If%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1551665%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1551665%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3BThank%20you!%20Is%20there%20a%20way%20to%20do%20this%20multiple%20times%20with%20multiple%20different%20cells%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1551736%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1551736%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F742859%22%20target%3D%22_blank%22%3E%40emlicata%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYes%3B%20let's%20say%20you%20want%20to%20'link'%20all%20cells%20in%20A1%3AZ1%20with%20the%20corresponding%20cells%20in%20A2%3AZ2.%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%0A%20%20%20%20If%20Target.CountLarge%20%26gt%3B%201%20Then%20Exit%20Sub%0A%20%20%20%20If%20Not%20Intersect(Range(%22A1%3AZ1%22)%2C%20Target)%20Is%20Nothing%20Then%0A%20%20%20%20%20%20%20%20Application.EnableEvents%20%3D%20False%0A%20%20%20%20%20%20%20%20Target.Offset(1%2C%200).Value%20%3D%20Target.Value%0A%20%20%20%20%20%20%20%20Application.EnableEvents%20%3D%20True%0A%20%20%20%20ElseIf%20Not%20Intersect(Range(%22A2%3AZ2%22)%2C%20Target)%20Is%20Nothing%20Then%0A%20%20%20%20%20%20%20%20Application.EnableEvents%20%3D%20False%0A%20%20%20%20%20%20%20%20Target.Offset(-1%2C%200).Value%20%3D%20Target.Value%0A%20%20%20%20%20%20%20%20Application.EnableEvents%20%3D%20True%0A%20%20%20%20End%20If%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Is there a way to make 2 cells equal eachother? Like can I have A1 = A2 and A2 = A1? I need to be able to change the number in either one of them and have them change. For example, right now I can make A1 = A2. So if A2 has the number 2, then A1 is number 2. But I also need to be able to change A1 to 1 and have A2 change to 1. 

3 Replies
Highlighted

@emlicata 

You need some VBA code for this.

Warning: you won't be able to use Undo when you change A1 or A2.

  • Right-click the sheet tab.
  • Select 'View Code' from the context menu.
  • Copy the code listed below into the worksheet module.
  • Press Alt+F4 to close the Visual Basic Editor.
  • Save the workbook as a macro-enabled workbook.
  • Make sure that you allow macros when you open the workbook.

 

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Address = "$A$1" Then
        Application.EnableEvents = False
        Range("A2").Value = Range("A1").Value
        Application.EnableEvents = True
    ElseIf Target.Address = "$A$2" Then
        Application.EnableEvents = False
        Range("A1").Value = Range("A2").Value
        Application.EnableEvents = True
    End If
End Sub
Highlighted

@Hans Vogelaar Thank you! Is there a way to do this multiple times with multiple different cells?

 

Highlighted

@emlicata 

Yes; let's say you want to 'link' all cells in A1:Z1 with the corresponding cells in A2:Z2.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Not Intersect(Range("A1:Z1"), Target) Is Nothing Then
        Application.EnableEvents = False
        Target.Offset(1, 0).Value = Target.Value
        Application.EnableEvents = True
    ElseIf Not Intersect(Range("A2:Z2"), Target) Is Nothing Then
        Application.EnableEvents = False
        Target.Offset(-1, 0).Value = Target.Value
        Application.EnableEvents = True
    End If
End Sub