Conditional Format

[kashyap]

Hi, I was trying to apply conditional format for A2. That is if the value of A2 repeats in either Column D or Column H or Column L, A2 should be highlighted.

 

[Sandeep Warrier]

In the conditional format box select the option Formula is and put the formula

=OR(COUNTIF($D:$D,A2)>=1,COUNTIF($H:$H,A2)>=1,COUNTIF($L:$L,A2)>=1)

and click ok

copy down to the other cells.

 

[kashyap]

I tried below formula.. but not working..

=or(countif($A:$a,A3)>=1,countif($e:$e,A3)>=1,countif($i:$i,A3)>=1,
countif($m:$m,A3)>=1,countif($q:$q,A3)>=1,countif($u:$u,A3)>=1,
countif($y:$y,A3)>=1,countif($ac:$ac,A3)>=1,countif($ag:$ag,A3)>=1,
countif($ak:$ak,A3)>=1,countif($ao:$ao,A3)>=1,countif($as:$as,A3)>=1,
countif($aw:$aw,A3)>=1,countif($ba:$ba,A3)>=1)

 

[Sandeep Warrier]

It is not working cuz of =or(countif($A:$a,A3)>=1

The Criteria range (A3) occurs within the range to be checked (Column A).

It works find if you remove the 1st condition.

=OR(COUNTIF($E:$E,A3)>=1,COUNTIF($I:$I,A3)>=1,COUNTIF($M:$M,A3)>=1,COUNTIF($Q:$Q,A3)>=1,COUNTIF($U:$U,A3)>=1,COUNTIF($Y:$Y,A3)>=1,COUNTIF($AC:$AC,A3)>=1,COUNTIF($AG:$AG,A3)>=1,COUNTIF($AK:$AK,A3)>=1,COUNTIF($AO:$AO,A3)>=1,COUNTIF($AS:$AS,A3)>=1,COUNTIF($AW:$AW,A3)>=1,COUNTIF($BA:$BA,A3)>=1)

 

[kashyap]

still not working.. not able enter formula in formula bar..

 

[MrRajKumar]

Kashyab,

There is a limitation of charractors in Conditional format box. So in A1 use this formula it will give you TRUE or FALSE. then in A3 make conditional format =A1=TRUE. You can hide this row, if you want.

 

[Sandeep Warrier]

Kashyab,

There is a limitation of charractors in Conditional format box. So in A1 use this formula it will give you TRUE or FALSE. then in A3 make conditional format =A1=TRUE. You can hide this row, if you want.

I completely forgot about the limitation.