Help with this program

Puggwash

Board Regular
Joined
Feb 13, 2005
Messages
189
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.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Puggwash

Board Regular
Joined
Feb 13, 2005
Messages
189
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
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.
Code:
=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

Board Regular
Joined
Feb 13, 2005
Messages
189
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

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814

ADVERTISEMENT

Hi

Try the MOD function

=mod((P67-B43),135)

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


HTH

Tony
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
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

Board Regular
Joined
Feb 13, 2005
Messages
189
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
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?
 

Forum statistics

Threads
1,141,679
Messages
5,707,786
Members
421,527
Latest member
Tamiwsw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top