I've compiled a spreadsheet of part numbers our company has at an off-site
warehouse, and to make reading this sheet easier, I'm trying to figure out a
way to alternate the background color of the rows based on the part number.

There may be six rows of one part number, then four rows of the next part
number, then ten rows of the next. I don't want to alternate the colours of
every other row because that has no logical relationship to the data. I want
the rows of the same part number to be the same color as each other, and then
to switch to an alternate color for the next part number's rows, and then
switch back to the first color for the folowing part number's rows, so all
rows of a unique part number are grouped by color and easily seen at a
glance. Is this even possible at all?

I've looked into conditional formatting, but I can't figure out what would
be needed to scan for occurrences of a part number, color them all the same,
then switch to a second color when the part number changes, and then switch
back to the first color when the part number changes again. I may be
attempting to do soemthing that just isn't possible. Does anyone have any
ideas?

Thanks!

You can do this if you use a helper column.

Assume your part numbers are in the range A2:A20

Enter a X in B2

Enter this formula in B3 and copy down to B20:

=IF(A3=A2,B2,IF(B2="X","Y","X"))

Now, set the conditional formatting:

Select the range A2:A20
Goto FormatConditional Formatting
Condition 1
Formula Is: =B2="X"
Click the Format button
Select a background colorOK
Click ADD
Condition 2
Formula Is: =B2="Y"
Click the Format button
Select a background colorOK out

Biff

"WhiteFantom" wrote in message
...
I've compiled a spreadsheet of part numbers our company has at an off-site
warehouse, and to make reading this sheet easier, I'm trying to figure out
a
way to alternate the background color of the rows based on the part
number.

There may be six rows of one part number, then four rows of the next part
number, then ten rows of the next. I don't want to alternate the colours
of
every other row because that has no logical relationship to the data. I
want
the rows of the same part number to be the same color as each other, and
then
to switch to an alternate color for the next part number's rows, and then
switch back to the first color for the folowing part number's rows, so all
rows of a unique part number are grouped by color and easily seen at a
glance. Is this even possible at all?

I've looked into conditional formatting, but I can't figure out what would
be needed to scan for occurrences of a part number, color them all the
same,
then switch to a second color when the part number changes, and then
switch
back to the first color when the part number changes again. I may be
attempting to do soemthing that just isn't possible. Does anyone have any
ideas?

Thanks!

The free Excel add-in "Shade Data Rows" will do that.
Download from ... http://www.realezsites.com/bus/primitivesoftware
No registration required.
--
Jim Cone
San Francisco, USA



"WhiteFantom"

wrote in message
I've compiled a spreadsheet of part numbers our company has at an off-site
warehouse, and to make reading this sheet easier, I'm trying to figure out a
way to alternate the background color of the rows based on the part number.

There may be six rows of one part number, then four rows of the next part
number, then ten rows of the next. I don't want to alternate the colours of
every other row because that has no logical relationship to the data. I want
the rows of the same part number to be the same color as each other, and then
to switch to an alternate color for the next part number's rows, and then
switch back to the first color for the folowing part number's rows, so all
rows of a unique part number are grouped by color and easily seen at a
glance. Is this even possible at all?

I've looked into conditional formatting, but I can't figure out what would
be needed to scan for occurrences of a part number, color them all the same,
then switch to a second color when the part number changes, and then switch
back to the first color when the part number changes again. I may be
attempting to do soemthing that just isn't possible. Does anyone have any
ideas?

Thanks!