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

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

milesUK

Active Member
Joined
Jan 5, 2003
Messages
388
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
Joined
Feb 13, 2005
Messages
189
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
Joined
Jan 5, 2003
Messages
388
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
Joined
May 28, 2005
Messages
49,545
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

ADVERTISEMENT

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
Joined
Jan 5, 2003
Messages
388
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
Joined
Feb 13, 2005
Messages
189
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
Joined
Feb 13, 2005
Messages
189
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.
 

Forum statistics

Threads
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.
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