search for value in cell located in adjacent column

[zraj07]

Column A has list of numbers. For simplicity, say they are 3, 15, 12, 16, 8.
Column B has a list of numbers. Say they are 1, 18, 27, 15, 8, 12, 16, 19, 34, 45...

Without changing the sort orders of these numbers, I am trying to write some code that will first consider Cell A1 (value of 3) , and will go search every value in Column B looking for a 3. If it finds any with a value of 3, it sets the interior.color to red.

Then, it would move to A2 (value of 15) and look for that in Column B, color them red if found, and move on to A3 (value of 12) and color them red.

I can't figure out how to use the cells method to keep track of where I am in Column A while searching Column B.

Any suggestions? Maybe I need to use some sort of Range operation?

 

[Jonmo1]

You can do this with conditional formatting, VBA not required...


Highlight all the values in Column B
Conditional Formatting
Formula
=MATCH(B1,$A$1:$A$5,0)
Format for red

Excel Workbook
	A	B
1	3	1
2	15	18
3	12	27
4	16	15
5	8	8
6		12
7		16
8		19
9		34
10		45
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1	1. / Formula is =MATCH(B1,$A$1:$A$5,0)	Abc
B2	1. / Formula is =MATCH(B2,$A$1:$A$5,0)	Abc
B3	1. / Formula is =MATCH(B3,$A$1:$A$5,0)	Abc
B4	1. / Formula is =MATCH(B4,$A$1:$A$5,0)	Abc
B5	1. / Formula is =MATCH(B5,$A$1:$A$5,0)	Abc
B6	1. / Formula is =MATCH(B6,$A$1:$A$5,0)	Abc
B7	1. / Formula is =MATCH(B7,$A$1:$A$5,0)	Abc
B8	1. / Formula is =MATCH(B8,$A$1:$A$5,0)	Abc
B9	1. / Formula is =MATCH(B9,$A$1:$A$5,0)	Abc
B10	1. / Formula is =MATCH(B10,$A$1:$A$5,0)	Abc