Jul 28 2020 01:55 PM
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.
Jul 28 2020 02:10 PM
You need some VBA code for this.
Warning: you won't be able to use Undo when you change A1 or A2.
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
Jul 28 2020 02:18 PM
@Hans Vogelaar Thank you! Is there a way to do this multiple times with multiple different cells?
Jul 28 2020 02:57 PM
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