Conditional formatting help

[Mark Christensen]

Hello,

I'm starting to bang my head against my desk with this one! I have 2 columns
of data: column A has 3500 rows; column C has 1800 rows. In column B I did a
vlookup so I know which cells in column A are in column C. The cells that
aren't in column C display #N/A in column B. I then copied column B and
pasted special as value so there are no formulas in column B. This part is
working as expected.

My problem is: now I want to conditional format column B so that all
occurrences of #N/A will be red shaded. For the life of me I can't get this
to work. Can someone please tell me how to do this?

I've tried by selecting column B, then going to conditional formatting,
choosing cell value is equal to #N/A and then on the format button,
selecting red as the cell shading color but nothing happens. Please help as
I'm losing my mind! Thanks.

Mark

 

[Trevor Shuttleworth]

Mark

Use:

Formula is: =ISNA($B2)

Not sure why you need column B ... you could do the conditional formatting
on column A.

Regards

Trevor

 

[Scoops]

Hello,

I'm starting to bang my head against my desk with this one! I have 2 columns
of data: column A has 3500 rows; column C has 1800 rows. In column B I did a
vlookup so I know which cells in column A are in column C. The cells that
aren't in column C display #N/A in column B. I then copied column B and
pasted special as value so there are no formulas in column B. This part is
working as expected.

My problem is: now I want to conditional format column B so that all
occurrences of #N/A will be red shaded. For the life of me I can't get this
to work. Can someone please tell me how to do this?

I've tried by selecting column B, then going to conditional formatting,
choosing cell value is equal to #N/A and then on the format button,
selecting red as the cell shading color but nothing happens. Please help as
I'm losing my mind! Thanks.

Mark

Hi Mark

In Conditional Formatting for cell B1:

Change "Cell Value Is" to "Formula Is".
Enter the formula =ISNA(B1).
Set your format and away you go.

Regards

Steve