Matching cells

[gary]

Col A has 200 cells (i.e., A1.A200)
Col B has 1500 cells (i.e., B1.B1500)

How can I find the cells in Col A whose contents match the contents in
the cells in Col B?

 

[OssieMac]

Hi Gary,

Easy enough to do but need more information on what you want to do when the
match is found.

Do you need to know if there is more than one match or if one match found is
that sufficient? (If one match is sufficient the VLOOKUP might so what you
want.)

What do you want to write against the cell where a match is found? (could
write the matching cell address/s if required.

Regards,

OssieMac

 

[Jim May]

Use Conditional Formatting is one-way.
While in Call A1 (activecell) Go to Format, Condtional Formatting
Choose FormulaIs and in Box put =COUNTIF(B:B,A1)>0
Use your Format Painter to Paint A1 to A2:A200

Jim May

 

[gary]

In Col A, the data occurs once
In Col B, the data may occur multiple times.

I need to know about ALL matches.

 

[OssieMac]

Hi again Gary,

The following macro will set the interior color of the cells in column A to
yellow if they are found in column B. It then inserts the address of the
column A value in column C adjacent to the found value (there could be
multiple occurrences of this address).

Sub Match_Values()

Dim rngA As Range
Dim rngB As Range
Dim CellA As Range
Dim foundCell As Range
Dim firstAddress As String

'Following assigns ranges of unknown length to
'variables starting from first cell of range.
With Sheets("Sheet1")
Set rngA = Range(.Cells(1, 1), _
.Cells(Rows.Count, 1).End(xlUp))
Set rngB = Range(.Cells(1, 2), _
.Cells(Rows.Count, 2).End(xlUp))
End With

'Alternative method of assigning ranges to a
'variable where the range is fixed and known.
'Set rngA = Sheets("Sheet1").Range("A1:A200")
'Set rngB = Sheets("Sheet1").Range("B1:B1500")

For Each CellA In rngA
With rngB
Set foundCell = .Find(What:=CellA, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If Not foundCell Is Nothing Then
firstAddress = foundCell.Address
Do
'Set interior color of found cell to yellow
CellA.Interior.ColorIndex = 6

'Insert cell address from column A
foundCell.Offset(0, 1) = CellA.Address

Set foundCell = .FindNext(foundCell)

Loop While Not foundCell Is Nothing And _
foundCell.Address <> firstAddress
End If
End With
Next CellA
End Sub

Regards,

OssieMac