Distribution of production to different production sites

[maywood]

Hi everyone,
again I have a question about programming a button in Excel 2003:

Let's say the production of a product increases in Dec.09 (Cell: G7) by 1000
pieces (Cell: G13). I have to 2 production sites which can produce the
product.
Now I want to implement a button where I can enter a distribution of the
1000 pieces, for example 70% to site 1 and 30% to site 2.
The macro should enter the number of pieces for site 1 (=700) to Cell G15
and 300 for site 2 to Cell G16 .

But there is an additional constraint:
Each site has a maximum capacity which cannot be exceded. Let's say site 1
has a capacity of 600 (Cell:G1) and site 2 a capacity of 500 (Cell:G2).
The macro should now distribute 600 pieces (=max. capacity) to site 1
(->60%) and tag the cell G15 with a red background because site 1 has reached
its maximum capacity. The macro should distribute the rest (40%=400) to site
2 (G16).
If each site reaches its maximum capacity the cells G15 & G16 should be
tagged red.

Q1: Which button should I use (the distribution should be variable each
month)?
Q2: Has someone an idea how to program the macro?

 

[joel]

This should be the code


Sub test()
Site1Percentage = 0.7
Site1Percentage = 0.3
Site1Max = Range("G1")
Site2Max = Range("G2")
ProductionChange = Range("G7")

If ProductionChange > Site1Max + Site2Max Then
Site1Increase = Site1Max
Site2Increase = Site2Max
Else
Site1Increase = ProductionChange * Site1Percentage
If Site1Increase >= Site1Max Then
Site1Increase = Site1Max
End If
Site2Increase = ProductionChange - Site1Increase
If Site2Increase >= Site2Max Then
Site2Increase = Site2Max
Site1Increase = ProductionChange - Site2Increase
End If
End If
Range("G15") = Site1Increase
Range("G16") = Site2Increase

If Site1Increase >= Site1Max Then
Range("G15").Interior.ColorIndex = 3
End If
If Site2Increase >= Site2Max Then
Range("G16").Interior.ColorIndex = 3
End If
End Su