Conditional format when #N/A

[Frantic Excel-er]

Hi,

I have a Vlookup function in column H of a worksheet (which is written in
code as part of a macro). I want to do conditional formatting if the vlookup
result is #N/A (which I would also like to be part of my macro). I need to
do this for all the cells in column H, not just one specific cell. In the
worksheet, I may or may not have a #N/A to highlight. Any help would be
GREATLY APPRECIATED...as I have been trying to figure this out for 3 hours
now.

Thanks,
Sara

 

[Joe]

if(iserror(vlookup(blah blah blah),1,vlookup(blah blah blah))

basically, this changes teh result of the formula to 1 instead of #N/A. Now
you can do a conditional format on the 1.

I'd say make the 1 a text string, but vba doesn't like quotes

 

[Frantic Excel-er]

Joe,

the problem occurs when the value in E isn't in my lookup table. Here is my
code:
With ActiveCell
RowCount = .Offset(0, -1).End(xlDown).Row - .Offset(0, -1).Row + 1
.FormulaR1C1 = _

"=IF(RC[-4]=""C"",VLOOKUP(RC[-3],USB,2,FALSE),VLOOKUP(RC[-3],FAS,2,FALSE))"
.AutoFill .Resize(RowCount)
End With

the formula is actually an if statement with 1 vlookup if column "C" has a
"C" in it, and another vlookup if it doesn't have a "C" in it. So, I am not
sure where the #N/A is coming from, but I believe it is because the value
isn't in the lookup table.

AAAAAGGGGHHHHH....

Any more ideas...I really appreciate the help....

 

[Bob Phillips]

How about just plain old vanilla

=ISNA(H1)

--
HTH

Bob Phillips

Joe,

the problem occurs when the value in E isn't in my lookup table. Here is my
code:
With ActiveCell
RowCount = .Offset(0, -1).End(xlDown).Row - .Offset(0, -1).Row + 1
.FormulaR1C1 = _

"=IF(RC[-4]=""C"",VLOOKUP(RC[-3],USB,2,FALSE),VLOOKUP(RC[-3],FAS,2,FALSE))"
.AutoFill .Resize(RowCount)
End With

the formula is actually an if statement with 1 vlookup if column "C" has a
"C" in it, and another vlookup if it doesn't have a "C" in it. So, I am not
sure where the #N/A is coming from, but I believe it is because the value
isn't in the lookup table.

AAAAAGGGGHHHHH....

Any more ideas...I really appreciate the help....

if(iserror(vlookup(blah blah blah),1,vlookup(blah blah blah))

basically, this changes teh result of the formula to 1 instead of #N/A. Now
you can do a conditional format on the 1.

I'd say make the 1 a text string, but vba doesn't like quotes

 

[Frantic Excel-er]

Bob.....

I want to thank you....I have tried that about 18000 times today, and it
didn't work....but I just now realized that I have to be on Cell H1, and then
highlight the whole column for it to work.....

AAAGGGHHH...can't believe I have wasted 4 hours of my life with such a
studip little detail....

Ah well.....guess you live and learn...

Can say that I have learned one thing from this...I will always make sure
that my cursor is on the 1st row of whichever column I am trying to format...

LOLOLOLOLOL
Thanks...
Sara

How about just plain old vanilla

=ISNA(H1)

--
HTH

Bob Phillips

Joe,

the problem occurs when the value in E isn't in my lookup table. Here is my
code:
With ActiveCell
RowCount = .Offset(0, -1).End(xlDown).Row - .Offset(0, -1).Row + 1
.FormulaR1C1 = _

"=IF(RC[-4]=""C"",VLOOKUP(RC[-3],USB,2,FALSE),VLOOKUP(RC[-3],FAS,2,FALSE))"
.AutoFill .Resize(RowCount)
End With

the formula is actually an if statement with 1 vlookup if column "C" has a
"C" in it, and another vlookup if it doesn't have a "C" in it. So, I am not
sure where the #N/A is coming from, but I believe it is because the value
isn't in the lookup table.

AAAAAGGGGHHHHH....

Any more ideas...I really appreciate the help....

if(iserror(vlookup(blah blah blah),1,vlookup(blah blah blah))

basically, this changes teh result of the formula to 1 instead of #N/A. Now
you can do a conditional format on the 1.

I'd say make the 1 a text string, but vba doesn't like quotes

:

Hi,

I have a Vlookup function in column H of a worksheet (which is written in
code as part of a macro). I want to do conditional formatting if the vlookup
result is #N/A (which I would also like to be part of my macro). I need to
do this for all the cells in column H, not just one specific cell. In the
worksheet, I may or may not have a #N/A to highlight. Any help would be
GREATLY APPRECIATED...as I have been trying to figure this out for 3 hours
now.

Thanks,
Sara

 

[Bob Phillips]

Glad to be of service

Bob.....

I want to thank you....I have tried that about 18000 times today, and it
didn't work....but I just now realized that I have to be on Cell H1, and then
highlight the whole column for it to work.....

AAAGGGHHH...can't believe I have wasted 4 hours of my life with such a
studip little detail....

Ah well.....guess you live and learn...

Can say that I have learned one thing from this...I will always make sure
that my cursor is on the 1st row of whichever column I am trying to format...

LOLOLOLOLOL
Thanks...
Sara

How about just plain old vanilla

=ISNA(H1)

--
HTH

Bob Phillips

Joe,

the problem occurs when the value in E isn't in my lookup table. Here

is
my

code:
With ActiveCell
RowCount = .Offset(0, -1).End(xlDown).Row - .Offset(0, -1).Row + 1
.FormulaR1C1 = _

"=IF(RC[-4]=""C"",VLOOKUP(RC[-3],USB,2,FALSE),VLOOKUP(RC[-3],FAS,2,FALSE))"

.AutoFill .Resize(RowCount)
End With

the formula is actually an if statement with 1 vlookup if column "C" has a
"C" in it, and another vlookup if it doesn't have a "C" in it. So, I

am
not

sure where the #N/A is coming from, but I believe it is because the value
isn't in the lookup table.

AAAAAGGGGHHHHH....

Any more ideas...I really appreciate the help....




:

if(iserror(vlookup(blah blah blah),1,vlookup(blah blah blah))

basically, this changes teh result of the formula to 1 instead of

#N/A.
Now

you can do a conditional format on the 1.

I'd say make the 1 a text string, but vba doesn't like quotes

:

Hi,

I have a Vlookup function in column H of a worksheet (which is

written
in

code as part of a macro). I want to do conditional formatting if

the
vlookup

result is #N/A (which I would also like to be part of my macro).

I
need to

do this for all the cells in column H, not just one specific cell.

In
the

worksheet, I may or may not have a #N/A to highlight. Any help

would
be

GREATLY APPRECIATED...as I have been trying to figure this out for

3
hours

now.

Thanks,
Sara