Conditional Formatting with "n/a"

[andrewc]

Hi,

In A3:A5 in the attached workbook I have some sample data that I'd like to format conditionally to highlight values larger than 75.

Any cells containing "n/a" should not be shaded but are.

Can someone please tell me what I'm doing wrong with the conditional formatting?

Thanks!

[Sam Capricci]

is your n/a the result of a formula or hand typed? Because if it is the result of a formula the cell doesn't change background color for me when it is the result of a formula and returns #N/A.

[andrewc]

It's the result of a formula

Thanks

[Sam Capricci]

then you should be ok? or is this still an issue?

[andrewc]

It's still an issue - I've uploaded a slightly amended workbook to illustrate better the issue; the "n/a" is the result of a formula

Thanks

[Sam Capricci]

change your vlookup to this... =IF(VLOOKUP(B4,D:E,2,FALSE)="",#N/A,VLOOKUP(B4,D:E,2,FALSE)) and it gets rid of the format issue.

[andrewc]

That works - thanks very much!

[Sam Capricci]

glad that worked for you, AND thank you for the rep!