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