Conditional Format

  • Thread starter Thread starter Kashyap
  • Start date Start date
K

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.
 
Give this Conditional Formatting formula a try...

=(COUNTIF(D:D,A2)+COUNTIF(H:H,A2)+COUNTIF(L:L,A2))>0
 
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)
 
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.
 
Kashyap said:
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)
Click to expand...


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))
 
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).
 
is there any character limitations in conditional formatting bar?
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel conditional formatting with Weeknum 4
Conditional Formatting - 2 columns 2
countifs Function not giving desired result with date criteria 2
Excel Conditional Formatting 1
Function Help - Forecast Date past due, adjacent cell is blank. Needto highlight the blank cell. 2
Apply Conditional Formatting to other rows 6
Getting a formula to do the following: 2
Conditional formatting of dates in two cells using Excel 2007 2

Back
Top