Highlighting rows in a range where ID code does not exist in secondrange...

[travis]

I need to highlight (e.g. by font or background colour) rows which
contain a value that is NOT present in a reference range.

Column S of the reference range contains an account ID code.

In the range being compared, I want to highlight new accounts which do
not exist in the reference range. The new range has a different
layout to the reference range because its a CSV file of payment data
downloaded from an assortment of third party web sites, but lets just
assume for the current example the ID code is in column C.

I know there are a variety of methods I could use including
highlighting all and then looping through the IDs in the reference
range, unhighlighting all rows where the ID appears in the new range.

But is that the fastest way?

What is the most efficient way to code for "if this ID code doesn't
appear in the reference list, highlight the row in bright blue"?

Travis

 

[Barb Reinhardt]

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.

 

[travis]

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

 

[Barb Reinhardt]

=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]

=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?

Actually, yes, it did. Thanks.

Travis