Try this one
Sub compare()
Const customerColumn = "A" 'Change to your needs
Const prospectsColumn = "B" 'Change to your needs
Const shName = "Sheet1" 'Change to your needs
Dim customerLastrspectsow As Long
Dim prospectsLastrow As Long
Dim customerPointer As Variant
Dim prospectsPointer As Variant
Dim isource As Long
Dim iCompare As Long

'lastrow for customer column
customerLastrow = Worksheets(shName).Range(customerColumn &
Rows.Count).End(xlUp).Row
'lastrow for prospects column
prospectsLastrow = Worksheets(shName).Range(prospectsColumn &
Rows.Count).End(xlUp).Row
'change 3 to the starting row of Customers
For isource = 3 To customerLastrow
Set customerPointer = Worksheets(shName).Cells(isource, 1)
iCompare = 3 'change 3 to the starting row of prospects
Do While iCompare <= prospectsLastrow
Set prospectsPointer = Worksheets(shName).Cells(iCompare, 2)
If prospectsPointer = customerPointer Then
prospectsPointer.Interior.ColorIndex = 6 'Yellow
End If
iCompare = iCompare + 1
Loop
Next isource
End Sub


"James" wrote:

Ok I have two columns:


A B
Customers: Prospects
Abc <highlightGhi<highlight
Def <highlightAbc<highlight
Ghi Tuv
Xyz
<highlightDef<highlight

I want to run a search against the customers in column A and to see if they
appear in the prospects column, if they do, I want to highlight them
automatically. It has to be done automatically because in the real list
there are about 140 customers and 4000+ prospects.

Thanks,

James