VBA Code Help for Circular Reference

Question

Good morning. Need some VBA help, if you don't mind. I'm needing a VBA code for a circular reference(?) and which works when there is data in either cell (remains blank if not). For example: if A1 has a word or content, then D1's content is that of A1, and if D1 has a word entered, then A1's content is that of D1 (otherwise the cells stay blank). I need to be able to type in either cell at any time without messing up or overriding the circular reference. Hope that makes sense. Thank you in advance.

Answer 1

Remove the line

    If Target.Count > 1 Then Exit Sub

Answer 2

My D1 is a group of merged cells (D1 is the start), which appears to be the problem. If I make a new book with only A1 and D1, it works perfectly. (I tried naming my range and substituting that name for "D1" but it still didn't work.) What would be the fix for that?

Answer 3

When I try the code, clearing D1 also clears A1. Could you please check again? If the problem remains, please make a sample workbook available through Dropbox, Google Drive or OneDrive.

Answer 4

Thanks, HansV. When typing in either: if I clear A1, then D1 clears, but if I clear D1, A1 still remains. Is there a fix for that?

Answer 5

Don't use formulas, only code.

Right-click the sheet tab and select 'View Code' from the context menu.

Copy the following code into the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Count > 1 Then Exit Sub

    If Not Intersect(Range("A1"), Target) Is Nothing Then

        Application.EnableEvents = False

        Range("D1").Value = Range("A1").Value

        Application.EnableEvents = True

    ElseIf Not Intersect(Range("D1"), Target) Is Nothing Then

        Application.EnableEvents = False

        Range("A1").Value = Range("D1").Value

        Application.EnableEvents = True

    End If

End Sub