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

  • Thread starter Thread starter jrg167
  • Start date Start date
J

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
 
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 __
 
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
 
Back
Top