Conditionally format cell to turn red after lookup match
 

I have a blank column A and a second column B with different alpha-numeric
17-characters in cells B1 to B50000. These are vehicle VINs.
Now, when I enter a VIN in A1, I would like to find if this VIN is in
column B and, if it is, turn the A1 cell red.
Next, I would enter a new VIN in A2 and again, check to see if it is in
column B and, if so, turn A2 red. I would continue this data entry down in
column A for up to 700-1000 VINs. I'd expect maybe 3-4 matches after entering
about 1000 VINs in column A.

Conditionally format cell to turn red after lookup match
 

Use conditional formatting with a formula of

=ISNUMBER(MATCH(A1,B:B,0))

--
__________________________________
HTH

Bob

"Dan H." wrote in message
...
I have a blank column A and a second column B with different alpha-numeric
17-characters in cells B1 to B50000. These are vehicle VINs.
Now, when I enter a VIN in A1, I would like to find if this VIN is in
column B and, if it is, turn the A1 cell red.
Next, I would enter a new VIN in A2 and again, check to see if it is in
column B and, if so, turn A2 red. I would continue this data entry down in
column A for up to 700-1000 VINs. I'd expect maybe 3-4 matches after
entering
about 1000 VINs in column A.

Conditionally format cell to turn red after lookup match
 

Give this a try:

1. Highlight Column A
2. Format | Conditional Formating
3. In Condition 1, change to "Formula Is"
4. Enter =ISNUMBER(MATCH(A1,B:B,0)) as the formula
5. With the Format button, select the Patterns tab and use your Red Cell
shading

Hope this helps.
--
Cordell


"Dan H." wrote:

I have a blank column A and a second column B with different alpha-numeric
17-characters in cells B1 to B50000. These are vehicle VINs.
Now, when I enter a VIN in A1, I would like to find if this VIN is in
column B and, if it is, turn the A1 cell red.
Next, I would enter a new VIN in A2 and again, check to see if it is in
column B and, if so, turn A2 red. I would continue this data entry down in
column A for up to 700-1000 VINs. I'd expect maybe 3-4 matches after entering
about 1000 VINs in column A.

Conditionally format cell to turn red after lookup match
 

While it might be proper for pedagogical reasons when used for conditional
formatting the ISNUMBER part is not necessary. MATCH alone will suffice, it
seems like any number will be the equivalent of TRUE and an error would be
the equivalent of FALSE.

--


Regards,


Peo Sjoblom

"Bob Phillips" wrote in message
...
Use conditional formatting with a formula of

=ISNUMBER(MATCH(A1,B:B,0))

--
__________________________________
HTH

Bob

"Dan H." wrote in message
...
I have a blank column A and a second column B with different alpha-numeric
17-characters in cells B1 to B50000. These are vehicle VINs.
Now, when I enter a VIN in A1, I would like to find if this VIN is in
column B and, if it is, turn the A1 cell red.
Next, I would enter a new VIN in A2 and again, check to see if it is in
column B and, if so, turn A2 red. I would continue this data entry down
in
column A for up to 700-1000 VINs. I'd expect maybe 3-4 matches after
entering
about 1000 VINs in column A.

Conditionally format cell to turn red after lookup match
 

Thanks folks! That worked perfectly.
In real life I'll have about 100,000 VINS in the lookup column.

"Peo Sjoblom" wrote:

While it might be proper for pedagogical reasons when used for conditional
formatting the ISNUMBER part is not necessary. MATCH alone will suffice, it
seems like any number will be the equivalent of TRUE and an error would be
the equivalent of FALSE.

--


Regards,


Peo Sjoblom

"Bob Phillips" wrote in message
...
Use conditional formatting with a formula of

=ISNUMBER(MATCH(A1,B:B,0))

--
__________________________________
HTH

Bob

"Dan H." wrote in message
...
I have a blank column A and a second column B with different alpha-numeric
17-characters in cells B1 to B50000. These are vehicle VINs.
Now, when I enter a VIN in A1, I would like to find if this VIN is in
column B and, if it is, turn the A1 cell red.
Next, I would enter a new VIN in A2 and again, check to see if it is in
column B and, if so, turn A2 red. I would continue this data entry down
in
column A for up to 700-1000 VINs. I'd expect maybe 3-4 matches after
entering
about 1000 VINs in column A.

Conditionally format cell to turn red after lookup match
 

I know that is so Peo, but I don't see it documented anywhere, so I would
rather be safe than trust MS to ensure it is always so

--
__________________________________
HTH

Bob

"Peo Sjoblom" wrote in message
...
While it might be proper for pedagogical reasons when used for conditional
formatting the ISNUMBER part is not necessary. MATCH alone will suffice,
it seems like any number will be the equivalent of TRUE and an error would
be the equivalent of FALSE.

--


Regards,


Peo Sjoblom

"Bob Phillips" wrote in message
...
Use conditional formatting with a formula of

=ISNUMBER(MATCH(A1,B:B,0))

--
__________________________________
HTH

Bob

"Dan H." wrote in message
...
I have a blank column A and a second column B with different
alpha-numeric
17-characters in cells B1 to B50000. These are vehicle VINs.
Now, when I enter a VIN in A1, I would like to find if this VIN is in
column B and, if it is, turn the A1 cell red.
Next, I would enter a new VIN in A2 and again, check to see if it is in
column B and, if so, turn A2 red. I would continue this data entry down
in
column A for up to 700-1000 VINs. I'd expect maybe 3-4 matches after
entering
about 1000 VINs in column A.