Solved: Find Multiple Values in Column and Replace Formatting in Different List

[ranuse]

Hi,

I'm having trouble implementing a macro that will look at sheet1 column A as source/reference column and replace the formatting (cell fill color) of sheet2 column B depending on which values are in A.

For example,

Sheet1 Column A (a subset of Sheet2 Column B) contains:
1
3
4
5
12
7

Sheet2 Column B contains:
0
1
2
11
3
4
5
20
12
7

I want to do a find value on Sheet1 Column A and replace formatting of the same value located in Sheet2 Column B.

Any help is appreciated.
Thanks.

[mdmackillop]

You'll need to set the ranges to suit.


Sub Test()
Dim Rng1 As Range, rng2 As Range, cel As Range
Set Rng1 = Sheets(1).Cells(1, 1).CurrentRegion
Set rng2 = Sheets(2).Cells(1,1).CurrentRegion
For Each cel In rng2
If Application.CountIf(Rng1, cel)> 0 Then cel.Interior.ColorIndex = 6
Next
End Sub

[ranuse]

Thanks alot, worked exactly as I wanted.