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" wrote in 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

sorry, instead of ISERROR
use ISNA

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"JulieD" wrote in message
...
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" wrote in 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

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" wrote in message
...
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" wrote in 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