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

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

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

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

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

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

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