# condition formatting

#### Puggwash

##### Board Regular
can I condition format cells that are not joined together in a worksheet when I enter a figure.
Lets say I have 280 cells and cell 1 is D6 and it goes down to Cell 40 = D48. Cell 41 = G6 to Cell 80 = G48. Cell 81 = J6 down to Cell 120 = J48.

How do i condition the cells so the colour changes when I enter a figure to represent the amount of boxes I need?

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

#### milesUK

##### Active Member
Conditional Formatting acts by:
• comparing the contents of the cell against some criteria and applying the format
or avaluating a formula that evaluates against the contents of other cells and/or the one being formatted
If I read it right you are entering a value, x, in a Qty cell and you want the first x cells to be formatted in some way. For example if 49 was put in say A1 the first block of 40 cells plus the first 9 of the 2nd block would be formatted. Is that correct?

#### Puggwash

##### Board Regular
Yes that is correct but do not know how to do it, will it work with any quantity i have so that the boxes auto colour?

#### milesUK

##### Active Member
This may do the trick. Conditionally Format cell D6: Select Formula Is in the drop down box. Use the formula =(ROW(D6)-6)<\$A\$1 and copy down to D45 (not D48)

In cell G6 use this slightly modified formula and copy down again.
=(ROW(G6)-6)<\$A\$1-40

Repeat for the maximimum number of columns you may need.

A more complicated formula could manage the succesive columns automatically but I've kept it simple.

#### Peter_SSs

##### MrExcel MVP, Moderator

can I condition format cells that are not joined together in a worksheet when I enter a figure.
Lets say I have 280 cells and cell 1 is D6 and it goes down to Cell 40 = D48. Cell 41 = G6 to Cell 80 = G48. Cell 81 = J6 down to Cell 120 = J48.

How do i condition the cells so the colour changes when I enter a figure to represent the amount of boxes I need?
Puggwash

I have problem with the counting here. If cell 1 is D6, then I make cell 40 to be D45 (not D48) etc. Assuming the number is in A1 and the cells are in blocks of 40, try this.

1. Select D6:D45
2. Format|Conditional Formatting|Condition 1|Formula is: =INT(COLUMNS(\$D\$6:D6)/3)*40+ROWS(\$D\$6:D6)<=\$A\$1|Format...|Patterns|choose your colour|OK|OK
3. Use the Format Painter to copy the formats to G6:G45 and J6:J45

Now try changing the value in A1.

#### Puggwash

##### Board Regular
Miles, thanks will try it now and let you know.

Peter, the cells are correct as they go in 10's and there is a gap before you go from 11-20 and so on.

#### Puggwash

##### Board Regular

Hi just tried to do the formatting and came across a problem.

My rows run in 10's (so its D6-D15, D17-D26, D28-D37 and D39-D48) giving 40 cells. The the smae in column G, J, M, P, S and V.
This gives me 280 cells to work with.
How do i conditional format all the cells so that whatever figure is in A1 the cells colour (say Red) to the quantity entered?
AS1 = Qty/135 giving the number of cells coloured.

#### milesUK

##### Active Member
For D6 to 15 use =(ROW(D6)-6)<\$A\$1
D17 to 26 .... =(ROW(D17)-7)<\$A\$1
D28 to 37 .... =(ROW(D17)-8)<\$A\$1 and so on.

Then copy formatting of column D to G, J, M, P, S and V.

Like Peter showed a formula can be created to apply to a whole block but it gets more complicated as you introduce breaks in the rows as well as columns. If this is a one off sheet then the effort (albeit a nice challenge) outweighs the benefits.

#### Puggwash

##### Board Regular
now I have done this how do i do the following, each cell is = to 135 so the value in A1 (say 10,000) = 74 cells to colour and leave me the remainder. i have tried to apply but it does not work for me. Any help.

#### Puggwash

##### Board Regular
now I have done this how do i do the following, each cell is = to 135 so the value in A1 (say 10,000) = 74 cells to colour and leave me the remainder. i have tried to apply but it does not work for me. Any help.

Replies
3
Views
129
Replies
6
Views
350
Replies
1
Views
181
Replies
5
Views
461
Replies
10
Views
626

1,137,205
Messages
5,680,184
Members
419,887
Latest member
Vasokir

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