Yet another issue of alternating colors

[beerpiece]

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?

 

[keymaster]

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

 

[TessaES]

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?

 

[Hui]

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

 

[xld]

Hui,


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

 

[xld]

A solution without helper columns


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

 

[excelhero]

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.