Conditional formatting on cells with a VLOOKUP formula in them

[Guest]

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

 

[Jason Morin]

In CF, choose "Formula Is" and use:

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

HTH
Jason
Atlanta, GA

 

[JulieD]

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

 

[JulieD]

Hi Jenni

sorry, instead of ISERROR
use ISNA

 

[Guest]

Julie and Jason, thanks a million!

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