Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Format
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Format
Give this Conditional Formatting formula a try...
=(COUNTIF(D:D,A2)+COUNTIF(H:H,A2)+COUNTIF(L:L,A2)) 0 -- Rick (MVP - Excel) "Kashyap" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Format
I tried below formula.. but not working..
=or(countif($A:$a,A3)=1,countif($e:$e,A3)=1,coun tif($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,counti f($ag:$ag,A3)=1, countif($ak:$ak,A3)=1,countif($ao:$ao,A3)=1,coun tif($as:$as,A3)=1, countif($aw:$aw,A3)=1,countif($ba:$ba,A3)=1) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Format
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. -- Rick (MVP - Excel) "Kashyap" wrote in message ... I tried below formula.. but not working.. =or(countif($A:$a,A3)=1,countif($e:$e,A3)=1,coun tif($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,counti f($ag:$ag,A3)=1, countif($ak:$ak,A3)=1,countif($ao:$ao,A3)=1,coun tif($as:$as,A3)=1, countif($aw:$aw,A3)=1,countif($ba:$ba,A3)=1) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Format
Kashyap wrote:
I tried below formula.. but not working.. =or(countif($A:$a,A3)=1,countif($e:$e,A3)=1,coun tif($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,counti f($ag:$ag,A3)=1, countif($ak:$ak,A3)=1,countif($ao:$ao,A3)=1,coun tif($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($A O:$AO,A3), COUNTIF($AS:$AS,A3),COUNTIF($AW:$AW,A3),COUNTIF($B A:$BA,A3)) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Format
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). -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... 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. -- Rick (MVP - Excel) "Kashyap" wrote in message ... I tried below formula.. but not working.. =or(countif($A:$a,A3)=1,countif($e:$e,A3)=1,coun tif($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,counti f($ag:$ag,A3)=1, countif($ak:$ak,A3)=1,countif($ao:$ao,A3)=1,coun tif($as:$as,A3)=1, countif($aw:$aw,A3)=1,countif($ba:$ba,A3)=1) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Format
is there any character limitations in conditional formatting bar?
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy conditional format without using format painter | Excel Worksheet Functions | |||
New Conditional Format Overriding Previous Conditional Format | Excel Discussion (Misc queries) | |||
How to create a conditional format that changes the number format | Excel Discussion (Misc queries) | |||
Conditional Format - Format Transfer To Chart | Excel Discussion (Misc queries) | |||
copy conditional format to regular format | Setting up and Configuration of Excel |