jam03
09-22-2008, 04:20 AM
Hi guys,
I know there should be a post on this somewhere but probably can't find the right keyword...
Anyway, for now, i'm trying to find the recurrent cells between two columns.
Not just compare the 1st cell of column1 with the 1st cell of column2 but to find if anywhere in column1, any cell exists in column2. so i came up with this small code :
Oh and let's say i've already ranged Column1 and Column2...
'Column1 and Column2 are ranged...
Dim r1, r2 As Range
For Each r1 In Column1
For Each r2 In Column2
If Column1.Cells(r1) = Column2.Cells(r2) Then
Column1.Cells(r1).Interior.Color = vbYellow
Column2.Cells(r2).Interior.Color = vbYellow
End If
Next
Next
End Sub
But yeah i suck at VBA and i can't find any good tutorial telling me what should i put in column1.cells(___).
I had made a previous code that worked but using Offsets and selection. Worked well but was way too slow and since i'm dealing with about 40 000 datas, Excel would crash. So i'm trying now with ranges and Each but...
Thank you in advance.
Cheers.
I know there should be a post on this somewhere but probably can't find the right keyword...
Anyway, for now, i'm trying to find the recurrent cells between two columns.
Not just compare the 1st cell of column1 with the 1st cell of column2 but to find if anywhere in column1, any cell exists in column2. so i came up with this small code :
Oh and let's say i've already ranged Column1 and Column2...
'Column1 and Column2 are ranged...
Dim r1, r2 As Range
For Each r1 In Column1
For Each r2 In Column2
If Column1.Cells(r1) = Column2.Cells(r2) Then
Column1.Cells(r1).Interior.Color = vbYellow
Column2.Cells(r2).Interior.Color = vbYellow
End If
Next
Next
End Sub
But yeah i suck at VBA and i can't find any good tutorial telling me what should i put in column1.cells(___).
I had made a previous code that worked but using Offsets and selection. Worked well but was way too slow and since i'm dealing with about 40 000 datas, Excel would crash. So i'm trying now with ranges and Each but...
Thank you in advance.
Cheers.