Forum Discussion
2 Way Cells
Without any details, we are forced to make assumptions:
1. Column A should add up to 100%
2. Column B should add up to the original number
3. You will not always want it calculated from 50
4. Cells A1 and A2 are formatted as Percentage
This should work for you, and give you some flexibility. Sheet 1, Cell E1 should be the "total" (start with 50 to match your example in the original post). The following should be saved in the code for the worksheet:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False 'Stops listening for changes until the script is finished. Keeps from getting stuck in a loop
If Not Intersect(Target, Range("$A$1:$B$2,$E$1")) Is Nothing Then 'If the changed cell is in the target range, proceed
If Range("$E$1").Value = 0 Then 'If cell E1 is 0, set the other 4 cells to blank, and skip to the end of the script
For Each c In Range("A1:B2")
c.Value = ""
Next
GoTo 9999
End If
Select Case Target.Address
Case "$A$1"
Range("$B$1").Value = Range("$A$1").Value * Range("$E$1").Value
Range("$B$2").Value = Range("$E$1").Value - Range("$B$1").Value
Range("$A$2").Value = Range("$B$2").Value / Range("$E$1").Value
Case "$A$2"
Range("$B$2").Value = Range("$A$2").Value * Range("$E$1").Value
Range("$B$1").Value = Range("$E$1").Value - Range("$B$2").Value
Range("$A$1").Value = Range("$B$1").Value / Range("$E$1").Value
Case "$B$1"
Range("$A$1").Value = Range("$B$1").Value / Range("$E$1").Value
Range("$B$2").Value = Range("$E$1").Value - Range("$B$1").Value
Range("$A$2").Value = Range("$B$2").Value / Range("$E$1").Value
Case "$B$2"
Range("$A$2").Value = Range("$B$2").Value / Range("$E$1").Value
Range("$B$1").Value = Range("$E$1").Value - Range("$B$2").Value
Range("$A$1").Value = Range("$B$1").Value / Range("$E$1").Value
Case "$E$1"
Range("$B$1").Value = Range("$A$1").Value * Range("$E$1").Value
Range("$B$2").Value = Range("$A$2").Value * Range("$E$1").Value
Range("$A$1").Value = Range("$B$1").Value / Range("$E$1").Value
Range("$A$2").Value = Range("$B$2").Value / Range("$E$1").Value
End Select
End If
9999:
Application.EnableEvents = True 'Resumes listening for changes to the target cells
End Sub
Mr. Dolinar
pls find as attached a sample of what I perceived to be your point.
in all those colored cells - you input any value and the other 3 cells changes correspondingly.
it may look crude - but I think it suits your need.
I intentionally locked all other cells - there is no password for the protection.
HTH