=ISNA(vlookup({the address of the cell containing ID105},S:S,1,false)).

Let's say you select all of the cells you want to check. For this example,
I'm going to say it's C1:C1000 and you start on cell C1

In the conditional format, put this

=ISNA(vlookup($C1,S:S,1,false)).

Does that work?
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"travis" wrote:

On Oct 1, 6:23 pm, Barb Reinhardt
wrote:
I'd probably do this with a Conditional Format and put something like this in
the Formula for the VLOOKUP

=ISNA(VLOOKUP(C1, ReferenceRange,1,False))

Change the VLOOKUP to suit. if you want to do this for all of column A for
example, highlight column A and put the first cell that you've selected in
the VLOOKUP. Make sure it looks something like $C1 with no $ before the row.

If you need a VBA example, come back.

Conditional format sounds like a good solution. How do I set it for a
large number of different values though?

Lets just say my reference range (range of customers already entered)
has a column with the following values:

ID101
ID102
ID106
ID108

And the range where I'm trying to find new values has the following
values

ID101
ID105
ID106
ID108

I'd want the entire row of the new range which has ID105 in it to be
highlighted red. That's easy enough for setting one row. I've
successfully done that by entering the formula =ISNA(vlookup({the
address of the cell containing ID105},S:S,1,false)).

That's one cell done. I can copy that formula for the remainder of
the row and then the row gets its highlight.

But how do I transpose it down? In the actual spreadsheet there are a
thousand rows being checked so manually changing the formula for each
of them is a pain.

I can of course write a macro that would apply the format
automatically. I set up a conditional format with the macro recorder
running and got some code that looks pretty easy to turn into a loop
that will run through the whole table setting up the conditional
formatting.

But maybe that isn't necessary. How do I copy the formula down so the
following row is formatted with the formula =ISNA(vlookup({the address
of the cell containing ID106},S:S,1,false))?

Travis