[SOLVED] Two cell values combined always equal 1

Two cell values combined always equal 1

I have two cells with percentage values that should always sum to 100%. I would like them to adjust automatically if either of the cells is adjusted.

I can only get it to adjust for one cell.

First attempt:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address ="$D$7" Then
    Range ("G7")="1-D7"
End If
End Sub

Works for change from D7 to G7. The other way around does not work. (I could change it to G7 but then I would have the same issue on D7.)

Second attempt:

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
    Case "$D$7":
        Range("G7") = "=1-D7"
    Case "$G$7":
        Range("D7") = "=1-G7"
End Select

End Sub

This would work, but I have the issue that the cases loop and thereby I lose the original value that I typed into D7 or G7.

Solution

EnableEvents set to False disable invoking the sub while changing a cell on the sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Select Case Target.Address
    Case "$D$7":
        Range("G7") = "=1-D7"
    Case "$G$7":
        Range("D7") = "=1-G7"
End Select
Application.EnableEvents = True

End Sub