Help with this program

[Puggwash]

hi, im wanting to find out how to work out a program to automatically colour my grids.

I have a worksheet that has 280 boxes (1-280) which we normally colour in when we complete the required order made. This is done in stages of 10's.

I have an 'Estimate' box (P64) that shows a figure (say 2000) and a 'Open Stock' box (B43) that shows a figure (say 200). Now my quantity I require is 1800 (Estimate minus Open Stock).

How do I work the program to work out what amount of boxes I need for the quantity required so it colours them automatically and we know where to go to without working it out.

Hope this makes sense.

 

[Puggwash]

update

hi to all those who ahve looked at my question. i will try again and hopefully make it simple..

I have 7 rows and 100 columns in which there is 280 boxes that calculate the quantity I/we need to make.
This is worked out by having a figure in P67 as the 'Estimate' and a figure in B43 as the 'Opening Stock'. When P67-B43 this gives us a total to make and we colour each of the boxes (in 10's) until we have made the amount required, then go onto something else.
I want the worksheet to work out the amount of boxes we need to colour without working it out.
So in simple terms: If P67(=20000) minus B43 (=200) this means I require 19800 to make of which each box represents 135.
This means I require 146 boxes to colour and leaves 90 left.
can someone help me with this 1st task so i can progress it further?

 

[HalfAce]

Hello puggwash,

I want the worksheet to work out the amount of boxes we need to colour without working it out.

If you're just looking for a way to know how many cells to color you could use a formula
like this somewhere in your sheet.

=ROUNDUP((P67-B43)/135,0.00001)

If you're looking for some code to automatically color that many cells then what range
is your 7 rows x 100 columns in, and what color do you want them to be?

 

[Puggwash]

hi the formula does not work as it rounds up. i want it to match the exact quantity of boxes (should be 20) and leave me remainder (42).

This I want to work out already without trying to calculate.

How do i send a copy across to you so you can see what Im talking about?

 

[acw]

Hi

Try the MOD function

=mod((P67-B43),135)

When P67 = 20000 and B43 is 200, the result is 90.


HTH

Tony

 

[HalfAce]

So in simple terms: If P67(=20000) minus B43 (=200) this means I require 19800 to make of which each box represents 135.
This means I require 146 boxes to colour and leaves 90 left.

Which is the value of interest, 146 or 90?
(I screwed up the first time & had 19800 in P67 which yeilded the result of 145.19)

I'm assuming you want to know how many cells to color (146) - right?
In order to get that I'm simply subtracting B43 (=200) from P67 (=20000) and getting
19800 which is then divided by 135 (=146.6667)
So (assuming you're interested in 146 and not the 90 left) do you want to round down then?

Now, if you're interested in the 90 cells left then Tony has shown the perfect way to
get that.

 

[Puggwash]

all this is correct, i do want the 146 boxes and to show the remainder of 90, BUT how do i get the formula to colour the boxes automatically without doing them myself?
tHIS MEANS THAT WHATEVER NUMBER I ENTER THE BOXES AUTO COLOUR.

Any help out there

Andy

 

[HalfAce]

If you're looking for some code to automatically color that many cells then what range
is your 7 rows x 100 columns in, and what color do you want them to be?

And with which cell do you want the code to execute upon making the entry, B43, or P67?