Compare Columns

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
Fri Mar 14 18:46:22 CDT 2008

Have you tried using Conditional Formatting on column B ?

Pete


On Mar 14, 11:38=A0pm, James <Ja...@discussions.microsoft.com> wrote:
> Ok I have two columns: =A0
>
> =A0 =A0 =A0 A =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0B
> Customers: =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Prospects
> Abc =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 <highlight>Ghi<highlig=
ht>
> Def =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 <highlight>Abc<highlig=
ht>
> Ghi =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 Tuv
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 Xyz
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 <highlight>Def<hig=
hlight>
>
> I want to run a search against the customers in column A and to see if the=
y
> appear in the prospects column, if they do, I want to highlight them
> automatically. =A0It has to be done automatically because in the real list=

> there are about 140 customers and 4000+ prospects.
>
> Thanks,
>
> James

Mike
Fri Mar 14 20:31:00 CDT 2008

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 <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
>

Mike
Fri Mar 14 20:46:00 CDT 2008

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 <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
Fri Mar 14 20:50:32 CDT 2008

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:

=3DCOUNTIF(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=A0pm, Pete_UK <pashu...@auditel.net> wrote:
> Have you tried using Conditional Formatting on column B ?
>
> Pete
>
> On Mar 14, 11:38=A0pm, James <Ja...@discussions.microsoft.com> wrote:
>
>
>
> > Ok I have two columns: =A0
>
> > =A0 =A0 =A0 A =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0B
> > Customers: =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Prospects
> > Abc =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 <highlight>Ghi<highl=
ight>
> > Def =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 <highlight>Abc<highl=
ight>
> > Ghi =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 Tuv
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 Xyz
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 <highlight>Def<h=
ighlight>
>
> > I want to run a search against the customers in column A and to see if t=
hey
> > appear in the prospects column, if they do, I want to highlight them
> > automatically. =A0It has to be done automatically because in the real li=
st
> > there are about 140 customers and 4000+ prospects.
>
> > Thanks,
>
> > James- Hide quoted text -
>
> - Show quoted text -

Mike
Fri Mar 14 20:50:00 CDT 2008

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 <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
>