http://chandoo.org/forums/topic/yet-another-issue-of-alternating-colors Ask Excel and Charting Questions, Muse about Visualizations, Learn and Share - Discussion Forums - Chandoo.org en-US Mon, 20 May 2013 22:12:27 +0000 http://bbpress.org/?v=1.0.1 q http://chandoo.org/forums/search.php http://chandoo.org/forums/topic/yet-another-issue-of-alternating-colors#post-3067 Mon, 13 Sep 2010 16:36:36 +0000 excelhero 3067@http://chandoo.org/forums/ <p>A simple way of doing this is:</p> <p>=MOD(SUMPRODUCT(--(($A$1:$A1=$A$2:$A2)=FALSE)),2)</p> <p>...but you must select row 2 and down before entering this conditional formatting formula.</p> <p>DO NOT INCLUDE ANY CONDITIONAL FORMATTING FOR ROW 1. </p> http://chandoo.org/forums/topic/yet-another-issue-of-alternating-colors#post-3066 Mon, 13 Sep 2010 15:19:19 +0000 xld 3066@http://chandoo.org/forums/ <p>A solution without helper columns</p> <p>=MOD(SUMPRODUCT(($A$1:$A1&lt;&gt;"")/COUNTIF($A$1:$A1,$A$1:$A1&amp;"")),2)=0 </p> http://chandoo.org/forums/topic/yet-another-issue-of-alternating-colors#post-3065 Mon, 13 Sep 2010 15:17:59 +0000 xld 3065@http://chandoo.org/forums/ <p>Hui,</p> <p>In some parts of the world, semi-colon is a list separator, not comma. </p> http://chandoo.org/forums/topic/yet-another-issue-of-alternating-colors#post-3062 Mon, 13 Sep 2010 13:40:29 +0000 Hui 3062@http://chandoo.org/forums/ <p>TessaES<br /> Do you mean<br /> =Mod(Char($A2),2)=0<br /> &amp;<br /> =Mod(Char($A2),2)=1</p> <p>of course this only applies if the characters Char values alternate even odd etc </p> http://chandoo.org/forums/topic/yet-another-issue-of-alternating-colors#post-3061 Mon, 13 Sep 2010 13:02:33 +0000 TessaES 3061@http://chandoo.org/forums/ <p>If you really have A,B,C,D etc in column A (but that is probably not the case), you could use:<br /> =MOD(CHAR(A2);2)=0 as formula for the conditional format.<br /> Maybe there is a similar option for your real data? </p> http://chandoo.org/forums/topic/yet-another-issue-of-alternating-colors#post-3058 Mon, 13 Sep 2010 11:53:22 +0000 Chandoo 3058@http://chandoo.org/forums/ <p>very interesting problem...</p> <p>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))</p> <p>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).</p> <p>hth </p> http://chandoo.org/forums/topic/yet-another-issue-of-alternating-colors#post-3057 Mon, 13 Sep 2010 11:42:33 +0000 beerpiece 3057@http://chandoo.org/forums/ <p>Hi,</p> <p>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.</p> <p>Col A<br /> A<br /> A<br /> A<br /> B<br /> B<br /> C<br /> C<br /> C<br /> C<br /> D<br /> E<br /> E<br /> F</p> <p>Row 1-3 grey, row 4-5 white, row 6-9 grey, row 10 white, row 11-12 grey, row 13 white.</p> <p>I have tried to read through all postings of CF, alternate coloring, countif's etc. but none seem to address this problem. </p> <p>How can this be accomplished? </p>