New Contributor

# Formula Question

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

# Re: Formula Question

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
Application.EnableEvents = False
Range("A2").Value = Range("A1").Value
Application.EnableEvents = True
Application.EnableEvents = False
Range("A1").Value = Range("A2").Value
Application.EnableEvents = True
End If
End Sub``````

# Re: Formula Question

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

# Re: Formula Question

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``````