Conditional format, Duplicates row colour.

[JimiH]

Hello

Excel 2007

When I apply CF to cells containing duplicate values the selection changs to the desired colour. I want excel to alternate between two colours so the duplicate values are easily identifiable in their groups.

See attached jpeg.

Thanks

Geoff

 

[HaHoBe]

Hi, Geoff ,

if you could use another cell - use column C for this and put a 1 in C1 and the formula into the next ones:

=IF(B1=B2,C1,IF(C1=1,2,1))

Then you can adjust to shade the ranges according to 1 and 2 in column C.

Ciao,
Holger

 

[JimiH]

Thanks worked perfectly

Geoff

 

[JimiH]

Hi

Instead of

=IF(B1=B2,C1,IF(C1=1,2,1))

How about if B1 was within +/- 10% of B2?

Thanks

Geoff

 

[HaHoBe]

Hi, Geoff,

as the value has to be inside a given range try this:

=IF(AND(B1>=B2*0.9,B1<=B2*1.1),C1,IF(C1=1,2,1))

HTH
Holger

 

[JimiH]

Once again, Thank You!