Conditional formatting on cells with a VLOOKUP formula in them

I have a worksheet with some cells that contain values generated from a
VLOOKUP formula. Some of these cells contain a #N/A error. (That's not the
problem.) I want to be able to use conditional formatting to turn the cells
containing #N/A red, but for some reason I can't make it work! Even on cells
that have values instead, a conditional format just won't work.

Is this because I'm trying to do it on VLOOKUPs? Is this an Excel glitch,
or is there something special I have to do?

Any help would be greatly appreciated!

Thanks,
JenniM

In CF, choose "Formula Is" and use:

=ISNA(INDIRECT("rc",0))

HTH
Jason
Atlanta, GA

>-----Original Message-----
>I have a worksheet with some cells that contain values
generated from a
>VLOOKUP formula. Some of these cells contain a #N/A
error. (That's not the
>problem.) I want to be able to use conditional
formatting to turn the cells
>containing #N/A red, but for some reason I can't make it
work! Even on cells
>that have values instead, a conditional format just
won't work.
>
>Is this because I'm trying to do it on VLOOKUPs? Is
this an Excel glitch,
>or is there something special I have to do?
>
>Any help would be greatly appreciated!
>
>Thanks,
>JenniM
>.
>

Hi Jenni

select the range you want to apply the conditional formatting to,
choose format /conditional formatting
choose formula is
type
=ISERROR($M2)
where M2 is the first cell in the selected range
click on format - set your formatting
click OK twice

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"JenniM" <[email protected]> wrote in message
news:[email protected]...
>I have a worksheet with some cells that contain values generated from a
> VLOOKUP formula. Some of these cells contain a #N/A error. (That's not
> the
> problem.) I want to be able to use conditional formatting to turn the
> cells
> containing #N/A red, but for some reason I can't make it work! Even on
> cells
> that have values instead, a conditional format just won't work.
>
> Is this because I'm trying to do it on VLOOKUPs? Is this an Excel glitch,
> or is there something special I have to do?
>
> Any help would be greatly appreciated!
>
> Thanks,
> JenniM

Hi Jenni

sorry, instead of ISERROR
use ISNA

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"JulieD" <[email protected]> wrote in message
news:[email protected]...
> Hi Jenni
>
> select the range you want to apply the conditional formatting to,
> choose format /conditional formatting
> choose formula is
> type
> =ISERROR($M2)
> where M2 is the first cell in the selected range
> click on format - set your formatting
> click OK twice
>
> --
> Cheers
> JulieD
> check out www.hcts.net.au/tipsandtricks.htm
> ...well i'm working on it anyway
> "JenniM" <[email protected]> wrote in message
> news:[email protected]...
>>I have a worksheet with some cells that contain values generated from a
>> VLOOKUP formula. Some of these cells contain a #N/A error. (That's not
>> the
>> problem.) I want to be able to use conditional formatting to turn the
>> cells
>> containing #N/A red, but for some reason I can't make it work! Even on
>> cells
>> that have values instead, a conditional format just won't work.
>>
>> Is this because I'm trying to do it on VLOOKUPs? Is this an Excel
>> glitch,
>> or is there something special I have to do?
>>
>> Any help would be greatly appreciated!
>>
>> Thanks,
>> JenniM
>
>

Julie and Jason, thanks a million!

It works like a charm, and now I don't have to dread the rest of my afternoon!

"JulieD" wrote:

> Hi Jenni
>
> sorry, instead of ISERROR
> use ISNA
>
> --
> Cheers
> JulieD
> check out www.hcts.net.au/tipsandtricks.htm
> ....well i'm working on it anyway
> "JulieD" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Jenni
> >
> > select the range you want to apply the conditional formatting to,
> > choose format /conditional formatting
> > choose formula is
> > type
> > =ISERROR($M2)
> > where M2 is the first cell in the selected range
> > click on format - set your formatting
> > click OK twice
> >
> > --
> > Cheers
> > JulieD
> > check out www.hcts.net.au/tipsandtricks.htm
> > ...well i'm working on it anyway
> > "JenniM" <[email protected]> wrote in message
> > news:[email protected]...
> >>I have a worksheet with some cells that contain values generated from a
> >> VLOOKUP formula. Some of these cells contain a #N/A error. (That's not
> >> the
> >> problem.) I want to be able to use conditional formatting to turn the
> >> cells
> >> containing #N/A red, but for some reason I can't make it work! Even on
> >> cells
> >> that have values instead, a conditional format just won't work.
> >>
> >> Is this because I'm trying to do it on VLOOKUPs? Is this an Excel
> >> glitch,
> >> or is there something special I have to do?
> >>
> >> Any help would be greatly appreciated!
> >>
> >> Thanks,
> >> JenniM
> >
> >
>
>
>