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
I have an idea.
Using VBA to write macro while changing A1 to B2 (either cells). You may refer to the attached file. The main code is stored in <workbook>. However, if you are considering more than 4 cells, the coding will be very complicated.
Also, I am unable to clear the cells. That's why I included another module to clear A1 to B2.
- how about the values that Mr. Dolinar stated?
maybe He should elaborate further to make it clear..
meaning - what is the relationship of each cell to each other.I do not know the relationship between the cells. I think he can modify the code by changing, for example, Range.("A1") to Range("A1").formula blah blah blah.
- hope that can help him.....
thanks
