• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Yet another issue of alternating colors

beerpiece

New Member
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?
 
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
 
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?
 
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
 
A solution without helper columns


=MOD(SUMPRODUCT(($A$1:$A1<>"")/COUNTIF($A$1:$A1,$A$1:$A1&"")),2)=0
 
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.
 
Back
Top