recurrence

[jam03]

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.

[Simon Lloyd]

Why not use conditional formatting?
Highlight all your cells give the range a name (say range1) then go to conditional formatting and use this =COUNTIF(range1,A1)>1 assuming your highlighted cells start from A1, then just choose the colour you want!

[Bob Phillips]

I agree 100% with Simon, but FYI, the code should look something like




'Column1 and Column2 are ranged...

Dim r1 As Range, r2 As Range

For Each r1 In Column1.Cells
For Each r2 In Column2.Cells

If r1.Value = r2.Value Then

r1.Interior.Color = vbYellow
r2.Interior.Color = vbYellow
End If
Next
Next


because you have already identified r1 and r2 are cells

[jam03]

I've used the code you gave me xld, works like a charm :cloud9: , I've understood now why I wasn't doing good... Thank you, I should spend more time on the resources though...
However, I didn't quite get what Simon told me, or the point, sorry. Is it so that i can just choose the colour? I'm really new with all that stuff.

I'm going to try to apply it now to the huge list I have. Hopefully it won't crash this time!

Many thanks

[Bob Phillips]

No, Simon was saying you don't need code, there is a built-in function for highlighting conditionally, called Conditional Formatting. See this site (http://www.xldynamic.com/source/xld.CF.html) for some ideas on it, and see if you can then see what Simon was alluding to.

[jam03]

Oh okay, thank you very much for such quick and clear answer.

And very useful link too...

Cheers