Comparison cell by cell

[Alexandre Vendemiatti]

Hi.

I have two columns "A1:A525" and "B1:B4339". I would like to know how can I get the value from "A1" and compare with all the values from column "B". I keep the line until the end of the "A" column. It would be something like this.

Column A	Column B
Company1	company99
Company2	company50
Company3	Company1

<tbody>
</tbody>

So, I would take the first data from the column "A" which is "Company1" and compare with all values from the column "B" to look for cells with the same value.Lets imagine that it was found the duplicate value as you can see in the table below. Then I would like only to mark the company that was found and keep looping.

Here what I got. I dont know how to go through all the datas from colum "A" one by one.
My code look for all datas in column B and when it founds the data I go to the Column A and change the color of the company that was found.

lastrow = Sheets(1).Cells(Sheets(1).Rows.Count, "B").End(xlUp).Row


For i = 1 To lastrow


If Sheets(1).Cells(i, 2).Value = "Company1" Then


Msgbox "Company found"



End If


Next

Please, I would be very thankful if you could give me only a tip of how to do that,

thank you very much,

Alexandre

 

[Mike LH]

Hi,

Try this code

Sub Find_It()
Dim lastrow As Long, MyRange As Range
Dim c As Range, Found As Range
lastrow = Cells(Rows.Count, "A").End(xlUp).Row#
Set MyRange = Range("A1:A" & lastrow)
For Each c In MyRange
    Set Found = Columns(2).Find(c.Value, LookIn:=xlValues, LookAt:=xlWhole)
If Not Found Is Nothing Then
    c.Font.Color = vbRed
End If
Next
End Sub

 

[Alexandre Vendemiatti]

Dude,

this worked perfectly.
Thank you a lot for the code. I will try to get familiar with it.

Thanks Again!!

Alexandre