Conditional Formatting with Colours / Colors

[mcarnold]

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.

 

[Max]

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

 

[mcarnold]

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]

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]

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)

---

 

[mcarnold]

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]

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

 

[mcarnold]

Thank you so much. This is beautiful! Perfect!

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]

Great to hear that !
Thanks for the feedback