condition formatting

Puggwash

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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

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
Back
Top