# Help with this program

#### Puggwash

##### Board Regular
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
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
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
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

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
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
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
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?

Replies
22
Views
2K
Replies
11
Views
280
Replies
99
Views
4K
Replies
3
Views
933
Replies
3
Views
370

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.

### Which adblocker are you using?

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

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