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.

 

[Rick Rothstein]

Give this Conditional Formatting formula a try...

=(COUNTIF(D,A2)+COUNTIF(H:H,A2)+COUNTIF(L:L,A2))>0

 

[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)

 

[Rick Rothstein]

That is different in construction from what I posted (I simply added up all
the COUNTIF functions to see if the total was greater than 0 (meaning at
least one match was found). However, at a quick glance, I don't really see
why the formula you used wouldn't work. Describe what you mean by "not
working" for us.

 

[Glenn]

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)

Well, your first test is always going to be true...A3 is always in column A.
And you can drop all of the ">=1".

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

 

[Rick Rothstein]

Okay, Glenn answered why my quick glance showed it working... I missed the
fact that COUNTIF($A:$a,A3) will always equal at least 1 (because A3's
content will always be found in the range A:A). The fix Glenn provided
should work for you (as would my addition method).

 

[Kashyap]

is there any character limitations in conditional formatting bar?