Hello,

I am familar with the formula and its variations where you can have
basic alternating colour / color rows. For example:
=MOD(ROW(),2)=1

would make Row 1, Row 3, Row 5, etc. colored.

I have a SUPER simple question, yet I can't find a simple answer
anywhere on here.

I need a formula that does essentially this, but switches colors for
the row when a new value is added in column "A." For example, if:

A B C
1 Tree Flower Mushroom
2 Tree Cat Dog
3 Rose Snow Star
4 Moon Sun Cloud
5 Star Fog Leaf
6 Star Fog Dog
7 Horse Cow Goat

I would want Rows 1 & 2 to be shaded (corresponding with the value in
column A), Row 3 to be unshaded, Row 4 to be shaded, Rows 5 & 6 to be
unshaded, Row 7 to be shaded and so on. Most importantly, if I were to
add a row after Row 4 for instance, then the color function would
automatically adjust.

Any help would be grand!

Thanks.

Presuming row1 is left empty (data from row2 down), this seems to work ok
Select cols A to C, then apply the cond format formula for condition 1 as:
=AND($A1<"",OR(OFFSET($A1,1,)=$A1,OFFSET($A1,-1,)=$A1))
Format the fill to taste, OK out
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
wrote in message
ups.com...
Hello,

I am familar with the formula and its variations where you can have
basic alternating colour / color rows. For example:
=MOD(ROW(),2)=1

would make Row 1, Row 3, Row 5, etc. colored.

I have a SUPER simple question, yet I can't find a simple answer
anywhere on here.

I need a formula that does essentially this, but switches colors for
the row when a new value is added in column "A." For example, if:

A B C
1 Tree Flower Mushroom
2 Tree Cat Dog
3 Rose Snow Star
4 Moon Sun Cloud
5 Star Fog Leaf
6 Star Fog Dog
7 Horse Cow Goat

I would want Rows 1 & 2 to be shaded (corresponding with the value in
column A), Row 3 to be unshaded, Row 4 to be shaded, Rows 5 & 6 to be
unshaded, Row 7 to be shaded and so on. Most importantly, if I were to
add a row after Row 4 for instance, then the color function would
automatically adjust.

Any help would be grand!

Thanks.

This is not working. Do I need to put something in the "" or the <?
Max wrote:
Presuming row1 is left empty (data from row2 down), this seems to work ok
Select cols A to C, then apply the cond format formula for condition 1 as:
=AND($A1<"",OR(OFFSET($A1,1,)=$A1,OFFSET($A1,-1,)=$A1))
Format the fill to taste, OK out
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
wrote in message
ups.com...
Hello,

I am familar with the formula and its variations where you can have
basic alternating colour / color rows. For example:
=MOD(ROW(),2)=1

would make Row 1, Row 3, Row 5, etc. colored.

I have a SUPER simple question, yet I can't find a simple answer
anywhere on here.

I need a formula that does essentially this, but switches colors for
the row when a new value is added in column "A." For example, if:

A B C
1 Tree Flower Mushroom
2 Tree Cat Dog
3 Rose Snow Star
4 Moon Sun Cloud
5 Star Fog Leaf
6 Star Fog Dog
7 Horse Cow Goat

I would want Rows 1 & 2 to be shaded (corresponding with the value in
column A), Row 3 to be unshaded, Row 4 to be shaded, Rows 5 & 6 to be
unshaded, Row 7 to be shaded and so on. Most importantly, if I were to
add a row after Row 4 for instance, then the color function would
automatically adjust.

Any help would be grand!

Thanks.

It should work fine. Perhaps something might have gone astray in your
implementation. Try it again, here's some elaboration on the steps

Select the col headers "A" to "C"
Click Format Conditional Formatting
Under Condition 1, make the settings as:
Formula is:
=AND($A1<"",OR(OFFSET($A1,1,)=$A1,OFFSET($A1,-1,)=$A1))
Click Format button Patterns tab Light brown? OK
Click OK at the main dialog

(Remember that row1 is presumed left empty)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
wrote in message
ups.com...
This is not working. Do I need to put something in the "" or the <?

Here's a working construct to illustrate:
http://www.savefile.com/files/259026
CF to color rows for identical successive col A values.xls
(with CF dialog screenshot)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Thank you for the construct, but even when I use as template it's not
working. Also, there is an error, because what I am looking for would
be for:

Tree: shaded
Tree: shaded
Rose: unshaded
Moon: shaded
Star: unshaded
Star: unshaded
Horse: shaded


Max wrote:
Here's a working construct to illustrate:
http://www.savefile.com/files/259026
CF to color rows for identical successive col A values.xls
(with CF dialog screenshot)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Sorry, I had misread the required alternating shading earlier. My error.

Try David McRitchie's excellent page at:
http://www.mvps.org/dmcritchie/excel/condfmt.htm

Scroll down about a 3rd of the page to:
Color Grouping with alternating colors (#grouping)

Applying it to your situation ..

Use a helper col D
In D1: 0
In D2, copied down:
=MOD(OFFSET($D2,-1,0)+OR($A2<OFFSET($A2,-1,0)),2)

Then CF formula applied with cols A to C selected:
=$D1=1
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
wrote in message
ups.com...
Thank you for the construct, but even when I use as template it's not
working. Also, there is an error, because what I am looking for would
be for:

Tree: shaded
Tree: shaded
Rose: unshaded
Moon: shaded
Star: unshaded
Star: unshaded
Horse: shaded

Thank you so much. This is beautiful! Perfect!

Max wrote:
Sorry, I had misread the required alternating shading earlier. My error.

Try David McRitchie's excellent page at:
http://www.mvps.org/dmcritchie/excel/condfmt.htm

Scroll down about a 3rd of the page to:
Color Grouping with alternating colors (#grouping)

Applying it to your situation ..

Use a helper col D
In D1: 0
In D2, copied down:
=MOD(OFFSET($D2,-1,0)+OR($A2<OFFSET($A2,-1,0)),2)

Then CF formula applied with cols A to C selected:
=$D1=1
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
wrote in message
ups.com...
Thank you for the construct, but even when I use as template it's not
working. Also, there is an error, because what I am looking for would
be for:

Tree: shaded
Tree: shaded
Rose: unshaded
Moon: shaded
Star: unshaded
Star: unshaded
Horse: shaded

Great to hear that !
Thanks for the feedback
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
wrote in message
ps.com...
Thank you so much. This is beautiful! Perfect!