Conditional formating using array formula??

[Andre Croteau]

Hi,

I have the following table:

col/ A B C
Row
1 Canada 1 ==> CF would be
BLUE
2 Canada 5 ==> CF would be
BLUE
3 Australia 1 ==> CF would
be GREEN
4 NewZealand 4 ==> CF would be
BLUE
5 NewZealand 6 ==> CF would be
BLUE
6 FrenchPolynesia 3 ==> CF would be
GREEN

I would like to use a conditional format in the cells in column B to
highlight every change in country, say alternate BLUE and GREEN
I am able to use the helper column A with this array formula:

{=ISEVEN(SUM(IF(FREQUENCY(IF(LEN(B$1:B1)>0,MATCH(B$1:B1,B$1:B1,0),""),IF(LEN(B$1:B1)>0,MATCH(B$1:B1,B$1:B1,0),""))>0,1)))}

which gives me a result of TRUE and FALSE every time there is a change.
Conditional formats is easy to use then. However, I would like to know if
it's possible to use a formula within the CF in cells of Column B. I tried
the array formula within the cell B1, but Excel wanted nothing of it!

Does anyone have a simple (or even complicated) solution ?

Thank you in advance

André

 

[Sloth]

highlight the list starting with A2. And insert this formula in the CF
"formula is" box. Set the default color as the first item in the list.

=MOD(SUMPRODUCT(--($A$2:$A2<>$A$1:$A1)),2)