Hi.
I have several numbers that sum up to 100 in excel spreadsheet. When a user changing one of the numbers in the excel worksheet I need automatic vba code that will change (proportionally) the values of rest of numbers in range proportionally so that they sum up to 100. The user can manually change any cell in the range - not just the first cell as shown in example below:

For example:

10
30
20
40
100

If the first excel cell is changed from 10 to 20:

20
30
20
40
110

The cell which was changed by user will remain the same value (the user can change any cell in range - not just the first cell as shown here) while the macro will change the values of rest of numbers in range proportionally so that they sum up to 100.

The result:

20
30/(110-20)*(100-20)=26.67
20/(110-20)*(100-20)=17.77
40/(110-20)*(100-20)=35.55
100


The following code works only if user changing the first cell in the range.

Sub test()

Dim lS As Long, rCell As Range
lS = Application.WorksheetFunction.Sum(Range("D7:D10"))

For Each rCell In Range("D8:D10")
rCell.Value = rCell.Value / (lS - Range("D7").Value) * (100 - Range("d7").Value)
Next

End Sub
Which vba code can identify and keep the cell value that was keyed in by user (the user can change any cell in range) while changing the rest of numbers in range proportionally so that they sum up to 100? Thanks.