VBA Code for Circular Reference / Cells Referencing Each Other

Question

Need some VBA help, if you don't mind. I've played with two other versions I've found online but can't get them to work for what I need. I'm needing a VBA code for a circular reference and which works when there is data in either cell (remains blank if not). If C20 has a number, then J13's formula is "=C20*12", and if J13 has the number, then C20's formula is "=J13/12". Otherwise the cells stay blank, though I can do without this part if necessary. But I need to be able to type in either cell at any time without messing up or overriding the formulas in the cells. Hope that makes sense. Thanks in advance.

Answer 1

Right-click the sheet tab.

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("C20"), Target) Is Nothing Then

        Application.EnableEvents = False

        If Range("C20").Value = "" Or Not IsNumeric(Range("C20").Value) Then

            Range("J13").ClearContents

        Else

            Range("J13").Value = Range("C20").Value * 12

        End If

        Application.EnableEvents = True

    End If

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

        Application.EnableEvents = False

        If Range("J13").Value = "" Or Not IsNumeric(Range("J13").Value) Then

            Range("C20").ClearContents

        Else

            Range("C20").Value = Range("J13").Value / 12

        End If

        Application.EnableEvents = True

    End If

End Sub

Answer 2

That is awesome. Thank you!