Yet another issue of alternating colors « Chandoo.org - Learn Excel & Charting Online

Chandoo.org - Learn Excel & Charting Online - Forums » Ask an Excel Question

Yet another issue of alternating colors

(7 posts) (6 voices)

Started 2 years ago by beerpiece
Latest reply from excelhero

Tags:

beerpiece

Member
Joined: Sep '10
Posts: 1

Hi,

I have a list of data (columns A to F), where the value of A changes without a set pattern and I need to change background color the rows on the "breaks" on the values of column A. So the end-result should be a variant of alternating colors, but not on every second or third row.

Col A
A
A
A
B
B
C
C
C
C
D
E
E
F

Row 1-3 grey, row 4-5 white, row 6-9 grey, row 10 white, row 11-12 grey, row 13 white.

I have tried to read through all postings of CF, alternate coloring, countif's etc. but none seem to address this problem.

How can this be accomplished?

Posted 2 years ago #
Chandoo

Key Master
Joined: Jul '09
Posts: 588

very interesting problem...

assuming your data is in A2:A14, in column B (I could not figure out a way without helper columns), write =IF(A2=A1,B1,NOT(B1))

This gives you a bunch of trues and falses. Now, select A2:A14 and format using values in column B (if B2=true set gray color, else it will be no-fill).

hth

Posted 2 years ago #
TessaES

Member
Joined: Nov '09
Posts: 35

If you really have A,B,C,D etc in column A (but that is probably not the case), you could use:
=MOD(CHAR(A2);2)=0 as formula for the conditional format.
Maybe there is a similar option for your real data?

Posted 2 years ago #
Hui

Excel Ninja
Joined: Jul '09
Posts: 5,053

TessaES
Do you mean
=Mod(Char($A2),2)=0
&
=Mod(Char($A2),2)=1

of course this only applies if the characters Char values alternate even odd etc

Posted 2 years ago #
xld

Member
Joined: Sep '10
Posts: 233

Hui,

In some parts of the world, semi-colon is a list separator, not comma.

Posted 2 years ago #
xld

Member
Joined: Sep '10
Posts: 233

A solution without helper columns

=MOD(SUMPRODUCT(($A$1:$A1<>"")/COUNTIF($A$1:$A1,$A$1:$A1&"")),2)=0

Posted 2 years ago #
excelhero

Member
Joined: Sep '10
Posts: 4

A simple way of doing this is:

=MOD(SUMPRODUCT(--(($A$1:$A1=$A$2:$A2)=FALSE)),2)

...but you must select row 2 and down before entering this conditional formatting formula.

DO NOT INCLUDE ANY CONDITIONAL FORMATTING FOR ROW 1.

Posted 2 years ago #