Need help with logic to spread changes
 

Here is a sample file with 4 columns
Gp Qty Change Reallocated
1 10 0
1 20 22.22222222
1 30 33.33333333
1 40 44.44444444
2 25
2 45
3
3
3

In the data above - Gp 1 has 100 units (10+20+30+40) allocated to it.
When a change to made to any of the quantities (changed to 0 from 10 in
row 1), that change needs to proportional distributed across the group
keeping the total the same. For instance if I reduce row 1 from 10 to
0, then row 2, 3 and 4 are changed to the numbers in the reallocated
column so that the sum is still 100 units.

Other groups are not affected. The change can be an increase or a
decrease.

I want to be able to do this through VBA - when a change is made to
column 3, I want to populate column 4.

Can someone please help?

Sai