Compare Columns

[James]

Ok I have two columns:


A B
Customers: Prospects
Abc <highlight>Ghi<highlight>
Def <highlight>Abc<highlight>
Ghi Tuv
Xyz
<highlight>Def<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

 

[Pete_UK]

Have you tried using Conditional Formatting on column B ?

Pete

 

[Mike]

Sub compare()
Const customerColumn = "A" 'Change to your needs
Const prospectsColumn = "B" '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(4).Range(customerColumn &
Rows.Count).End(xlUp).Row
'lastrow for prospects column
prospectsLastrow = Worksheets(4).Range(prospectsColumn &
Rows.Count).End(xlUp).Row
'change 3 to the starting row of Customers
For isource = 3 To customerLastrow
Set customerPointer = Worksheets(4).Cells(isource, 1)
iCompare = 3 'change 3 to the starting row of prospects
Do While iCompare <= prospectsLastrow
Set prospectsPointer = Worksheets(4).Cells(iCompare, 2)
If prospectsPointer = customerPointer Then
prospectsPointer.Interior.ColorIndex = 6 'Yellow
End If
iCompare = iCompare + 1
Loop
Next isource
End Sub

 

[Mike]

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

 

[Mike]

Sorry this will avoid null value
Sub compare()
Const customerColumn = "A" 'Change to your needs
Const prospectsColumn = "B" 'Change to your needs
Const shName = "Sheet4"
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 customerPointer > vbNull Then
If prospectsPointer = customerPointer Then
prospectsPointer.Interior.ColorIndex = 6 'Yellow
End If
End If
iCompare = iCompare + 1
Loop
Next isource
End Sub

 

[Pete_UK]

Highlight all the data in column B, starting with cell B2, and click
on Format | Conditional Formatting and in the panel that pops up
select Formula Is in the first box rather than Cell Value Is. In the
second box enter this formula:

=COUNTIF(A$2:A$150,B2)>0

then click the Format button, click on the Patterns tab, and then
choose your colour (eg bright yellow). Click OK twice to exit the
dialogue box, and then you should have what you want.

Hope this helps.

Pete