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é

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)

"Andre Croteau" wrote:

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é