EXCEL Conditional Formatting

[HANK]

I have two columns of numbers. One small set (25-200, imported daily) of
numbers in column B, and 8365 unique numbers in column K. I would like to
use conditional formatting to tell me if any of the numbers imported into
column B match any of the 8365 unique numbers in column K. If so, I would
like the numbers in column B that match to turn Red.

 

[Bernie Deitrick]

HANK,

Use CF with the Formula Is option, with a formula like

=NOT(ISERROR(MATCH(B2,K:K,False)))

where B2 is the activecell at the time that you apply the formatting.

And if the empty cells are turning red and your want to prevent that....

=AND(B2<>"",NOT(ISERROR(MATCH(B2,K:K,False))))

HTH,
Bernie
MS Excel MVP

 

[T. Valko]

Try this...

Select the range of cells. Assume this range is B2:B201
Goto the menu Format>Conditional Formatting
Select the Formula Is option
Enter this formula in the box on the right:
=MATCH(B2,K$2:K$8366,0)
Click the Format button
Select the desired style(s)
OK out

 

[Max]

One way

Select col B (B1 active),
apply CF using Formula Is:
=COUNTIF($K:$K,B1)
Format to taste > ok out

Yes? Pl click the YES button below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik