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

Have you tried using Conditional Formatting on column B ?

Pete


On Mar 14, 11:38*pm, 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

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

"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

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

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

"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

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

On Mar 14, 11:46*pm, Pete_UK wrote:
Have you tried using Conditional Formatting on column B ?

Pete

On Mar 14, 11:38*pm, 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- Hide quoted text -

- Show quoted text -