Conditional Formatting Range to Single Cell in Excel 2010

[shelby55]

Hello

I'm using Excel 2010.

I have a range of data from N73 to N103. If any one of those cells has a certain text in it ("BlueCross") and cell N16 indicates "N" then I want the cell to be red.

I can get it to work for a single cell to cell comparision i.e.
=if(and(find("BlueCross",N73,1),N16="N"),1,0) but can't get it to look in the range.

Is this possible or will I have to create a conditional format rule for all 30 cells of N73 to N103?

Thanks.

 

[SkipVought]

Hi,

First SELECT n73:n103.

Open the CF wizard

Enter this formula
[tt]
=and($n$16="N",n73="BlueCross")
[/tt]
And assign the format you want.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

Thanks for replying. Just to clarify, I want cell N16 to be red if it is "N" and any of the cells from N73 to N103 have BlueCross in it. The above appears to be making the cells of N73 to N103 red if this scenario is true...am I wrong on that? Also, I actually want where any name is starts with Blue i.e. BlueCross, BlueShield so won't I have to do the "find" function because there isn't a "starts with" one.

 

[SkipVought]

Then use the MATCH function to "find" BlueCross

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

[tt]
=and($n$16="N",or(not(isna(match("BlueCross",n73:n103,0))),not(isna(match("BlueShield",n73:n103,0)))))
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}