View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.misc
Andre Croteau
 
Posts: n/a
Default Conditional formating using array formula??

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é