| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Row shading based on criteria
Hi,
I would like to have rows shaded only if a certain number on a row changes: So, if the data was like the following: 50772 JONE RON 355-FT-DR 50772 JONE RON 355-FT-DR 50772 JONE RON 355-FT-DR 50772 JONE RON 355-FT-DR 19780 ASSETT JAMES 25-FT-DR 19780 ASSETT JAMES 25-FT-DR 19780 ASSETT JAMES 25-FT-DR 19780 ASSETT JAMES 25-FT-DR 19780 ASSETT JAMES 25-FT-DR 100156 BEVIS SHAWN 25-FT-DR 100156 BEVIS SHAWN 25-FT-DR rows 1-4 and 10-11 would be shaded, skipping the rest. I have about 250 lines in total with this kind of arrangement. I have been reading about conditional formatting, and have some ideas, but not quite there yet! Any ideas? Thanks, Eric in FL |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Row shading based on criteria
Hi,
Why are rows 1 - 4 shaded, what is the criteria? Mike "prana1" wrote: Hi, I would like to have rows shaded only if a certain number on a row changes: So, if the data was like the following: 50772 JONE RON 355-FT-DR 50772 JONE RON 355-FT-DR 50772 JONE RON 355-FT-DR 50772 JONE RON 355-FT-DR 19780 ASSETT JAMES 25-FT-DR 19780 ASSETT JAMES 25-FT-DR 19780 ASSETT JAMES 25-FT-DR 19780 ASSETT JAMES 25-FT-DR 19780 ASSETT JAMES 25-FT-DR 100156 BEVIS SHAWN 25-FT-DR 100156 BEVIS SHAWN 25-FT-DR rows 1-4 and 10-11 would be shaded, skipping the rest. I have about 250 lines in total with this kind of arrangement. I have been reading about conditional formatting, and have some ideas, but not quite there yet! Any ideas? Thanks, Eric in FL |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Row shading based on criteria
It might help if you told us what number makes rows 1-4, 10-11 worthy of
being shaded. Also, how is that data laid out (all in one cell? split between 3 cell? which is in what cells?). My guess is it has something to do with a "5" being in the first number (but is that number in its own cell?), but you should clarify your layout and requirements a little more beforehand. Rick "prana1" wrote in message ... Hi, I would like to have rows shaded only if a certain number on a row changes: So, if the data was like the following: 50772 JONE RON 355-FT-DR 50772 JONE RON 355-FT-DR 50772 JONE RON 355-FT-DR 50772 JONE RON 355-FT-DR 19780 ASSETT JAMES 25-FT-DR 19780 ASSETT JAMES 25-FT-DR 19780 ASSETT JAMES 25-FT-DR 19780 ASSETT JAMES 25-FT-DR 19780 ASSETT JAMES 25-FT-DR 100156 BEVIS SHAWN 25-FT-DR 100156 BEVIS SHAWN 25-FT-DR rows 1-4 and 10-11 would be shaded, skipping the rest. I have about 250 lines in total with this kind of arrangement. I have been reading about conditional formatting, and have some ideas, but not quite there yet! Any ideas? Thanks, Eric in FL |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Row shading based on criteria
Hi Mike,
I am looking for a "grouped banding", where it stripes rows with the same unique information, then skips shading for the next set, then shades the following set, etc. So, first it would shde the first four rows that all have the the number 50772. Then the next set of rows have a different unique numbers (19780), they would have no shading. Then the last 2 rows have a different unique number,100156, they would be shaded the same as the first set. Then no shading for the next set, and so on. Hope that makes sense. "Mike H" wrote: Hi, Why are rows 1 - 4 shaded, what is the criteria? Mike "prana1" wrote: Hi, I would like to have rows shaded only if a certain number on a row changes: So, if the data was like the following: 50772 JONE RON 355-FT-DR 50772 JONE RON 355-FT-DR 50772 JONE RON 355-FT-DR 50772 JONE RON 355-FT-DR 19780 ASSETT JAMES 25-FT-DR 19780 ASSETT JAMES 25-FT-DR 19780 ASSETT JAMES 25-FT-DR 19780 ASSETT JAMES 25-FT-DR 19780 ASSETT JAMES 25-FT-DR 100156 BEVIS SHAWN 25-FT-DR 100156 BEVIS SHAWN 25-FT-DR rows 1-4 and 10-11 would be shaded, skipping the rest. I have about 250 lines in total with this kind of arrangement. I have been reading about conditional formatting, and have some ideas, but not quite there yet! Any ideas? Thanks, Eric in FL |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Row shading based on criteria
I would suggest that you have a blank row above your data - you can
use it for headers. Keep E1 blank, then you can enter this formula in E2: =IF(A2<A1,NOT(E1),IF(A2=A1,E1)) Copy this formula down and you will have a series of TRUE and FALSE against the blocks of numbers in column A - you can use this column as the basis of your conditional formatting formula, i.e. highlight A2 to D250, with A2 being the active cell. Then click on Format | Condition Formatting and select Formula Is in the first box of the pop-up and enter this as the formula: =$E2 Click on the Format button, Patterns tab and select your colour. Then click OK twice to exit the dialogue box. You can hide column E if you don't want to see those TRUEs and FALSEs. Hope this helps. Pete On Mar 31, 4:57*pm, prana1 wrote: Hi, I would like to have rows shaded only if a certain number on a row changes: So, if the data was like the following: 50772 * JONE * *RON * * 355-FT-DR 50772 * JONE * *RON * * 355-FT-DR 50772 * JONE * *RON * * 355-FT-DR 50772 * JONE * *RON * * 355-FT-DR 19780 * ASSETT *JAMES * 25-FT-DR 19780 * ASSETT *JAMES * 25-FT-DR 19780 * ASSETT *JAMES * 25-FT-DR 19780 * ASSETT *JAMES * 25-FT-DR 19780 * ASSETT *JAMES * 25-FT-DR 100156 *BEVIS * SHAWN * 25-FT-DR 100156 *BEVIS * SHAWN * 25-FT-DR rows 1-4 and 10-11 would be shaded, skipping the rest. *I have about 250 lines in total with this kind of arrangement. I have been reading about conditional formatting, and have some ideas, but not quite there yet! *Any ideas? * Thanks, Eric in FL |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Row shading based on criteria
Hi Pranal
Assuming rows 5 to 9 is the criteria, go to Conditional Formatting,second window select "Not equal to" third window type in "19780 ASSETT JAMES 25-FT-DR" then press Format button, select "Patterns " and choose your colour. Regards Cimjet "prana1" wrote in message ... I would like to have rows shaded only if a certain number on a row changes: So, if the data was like the following: 50772 JONE RON 355-FT-DR 50772 JONE RON 355-FT-DR 50772 JONE RON 355-FT-DR 50772 JONE RON 355-FT-DR 19780 ASSETT JAMES 25-FT-DR 19780 ASSETT JAMES 25-FT-DR 19780 ASSETT JAMES 25-FT-DR 19780 ASSETT JAMES 25-FT-DR 19780 ASSETT JAMES 25-FT-DR 100156 BEVIS SHAWN 25-FT-DR 100156 BEVIS SHAWN 25-FT-DR rows 1-4 and 10-11 would be shaded, skipping the rest. I have about 250 lines in total with this kind of arrangement. I have been reading about conditional formatting, and have some ideas, but not quite there yet! Any ideas? Thanks, Eric in FL |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Row shading based on criteria
Eric, You can also check out the free trial of my Excel add-in "Shade Data Rows". Direct download from the products page at... http://www.realezsites.com/bus/primitivesoftware -- Jim Cone San Francisco, USA (Excel Add-ins / Excel Programming) "prana1" wrote in message Hi, I would like to have rows shaded only if a certain number on a row changes: So, if the data was like the following: 50772 JONE RON 355-FT-DR 50772 JONE RON 355-FT-DR 50772 JONE RON 355-FT-DR 50772 JONE RON 355-FT-DR 19780 ASSETT JAMES 25-FT-DR 19780 ASSETT JAMES 25-FT-DR 19780 ASSETT JAMES 25-FT-DR 19780 ASSETT JAMES 25-FT-DR 19780 ASSETT JAMES 25-FT-DR 100156 BEVIS SHAWN 25-FT-DR 100156 BEVIS SHAWN 25-FT-DR rows 1-4 and 10-11 would be shaded, skipping the rest. I have about 250 lines in total with this kind of arrangement. I have been reading about conditional formatting, and have some ideas, but not quite there yet! Any ideas? Thanks, Eric in FL |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Is there a way to add cells based on their shading / fill colour? | Excel Discussion (Misc queries) | |||
| Conditional Cell Shading (based on the shading of other cells) | Excel Worksheet Functions | |||
| Automatic shading of cells based on dates??? | Excel Worksheet Functions | |||
| how can I use shading as criteria in a formula? | Excel Worksheet Functions | |||
| how do you set auto shading of cell based on percent value in exc. | Excel Worksheet Functions | |||
Linear Mode
