Conditional formatting help

[jonandabby]

I've got two lists of file numbers - one short one (109) in column B of a spreadsheet, and a VERY long one (60,000 long) in column B. What I'd like to do is highlight the value in column B every time any element in column A shows up. I've been trying =countif($a:$a,b2), but the results are off a bit (if I sort the file numbers and e.g. there are three that match, the function is highlighting the one RIGHT BEFORE the list starts, and skipping the last one.)

Any suggestions????

An example

Column A Column B
AS418 AS417
AS418
AS418
AS418

 

[VoG]

Try

=ISNUMBER(MATCH(B2,A:A,0))

 

[jonandabby]

Try

=ISNUMBER(MATCH(B2,A:A,0))

Does the same **** thing. Highlights the one before, and skips the last one.

 

[jonandabby]

By the way, I rebooted, and tried again. Same result.

 

[VoG]

Then maybe start in B1 and use B1 in the formula.

 

[jonandabby]

Perfect. Thanks!

 

[jonandabby]

Let's really challenge folks. The reason for the conditional formatting is that I wanted to be able to change the value in column c right next to the column B entry to a particular text value ("Open") where it now is "Closed"). Is there a way to do that? Maybe using an if then statement?

The summary of the rule I'm trying to use would be is "if the value of anything in B### = the value of ANYTHING In column A, then if the value in C### = "Closed" change the value in C### to "Open").

 

[VoG]

That would create a circular reference. Why not just

=IF(ISNUMBER(MATCH(B1,A:A,0)),"Open","Closed")

 

[jonandabby]

The words open and closed appear in other columns - the spreadsheet is bigger than those three columns (30 columns or so), and the names of the files can contain the word too. I just need to change the value right next to the individual file references in column B.