getting cell color to allow alternating blocks of like value == likecolor cells

[jrg167]

I have seen this asked a few places and some answers, but nothing that
works. I want to simply alternate the color of a col so blocks of
identical values have the same color. Thus

A black
A black
B blue
C black
C black
A blue
B black
C blue
C blue
C blue

If you could get the cell color in a formula you could do it painfully
as in a formula for black and a formula for blue applied to all but
the first row. So the formulas would be along the lines of
if x!=above cell and above cell is blue or x==above and above cell is
black -> format black
if x!=above cell and above cell is black or x == above cell and above
cell is blue -> format blue

But extracting the color of a cell in a formula does not seem to be
available.

Excel 2008 for Mac version 12.1.1

 

[T. Valko]

See this:

http://tinyurl.com/5g35f3

 

[jrg167]

See this:

http://tinyurl.com/5g35f3

Thanks. I replied in that thread (the MOD method didn't work and I
don't like the help cell method).

 

[Héctor Miguel]

hi, !

assuming row1 for titles and data starting on row2 (and column A)...
try the following formula for your conditional formats (A2 as the active cell)

=mod(sumproduct(--($a$1:$a1<>$a$2:$a2)),2)

regards,
hector.

__ OP __

 

[Eric]

Free addin ASAP Utilities can operate on background or text color.

Combine it with conditional formatting formula on color AND change in value
of previous row.

Eric