compare columns for duplicates

[Snabelhund]

Hello, i´m stuck on a bit of code that i cant get my head around. Perhaps someone could help me abit.

I have tvo columns A (about 50 rows at the moment but can expand or decrease weekly) that i want to compare to column C (abot 500 rows but this can also vary, usually it should contain somewhere between 500-800 rows)

What i want to accomplish i to compare column A aginst column C too see if there is any duplicate cells in columnA. For example if any of the cells i column A contains the 123456-77 and the same string can be found in column C i would like to highligth the cell in column A.

I found some code that seems to be similar to my question, but I can only get this working partly. When i run the code it doesent seem to pick up all the duplicates ( only the first two get highlighted). If im doing a conditional formatting several more duplicates are higlighted. Unfourtunately i´m new to and still learning vba so i cant really tell if or what alterations that i should make to the code.

Sub color()
Dim d As Object, c
Dim nda As Long, ndb As Long

Set d = CreateObject("scripting.dictionary")
nda = Range("A" & Rows.Count).End(xlUp).Row
ndb = Range("C" & Rows.Count).End(xlUp).Row
For Each c In Range("C2:C" & nda)
    d(c.Value) = 1
Next c
For Each c In Range("A2:A" & ndb)
    If d(c.Value) = 1 Then c.Interior.Color = vbCyan
    If Len(c) = 0 Then
        c.Interior.Color = vbRed
      
        Exit Sub
    End If
Next c
End Sub

 

[Fluff]

Try removing this line

        Exit Sub

 

[Snabelhund]

Tried it, when i i remove the line it doesent seem to recognise any duplicates at all. Could it be that the code is not fit for the purpose?

 

[Fluff]

You also have the last row variables the wrong way round. This line should be ndb

For Each c In Range("C2:C" & nda)

and the same for the 2nd loop through col A

 

[Snabelhund]

You also have the last row variables the wrong way round. This line should be ndb

For Each c In Range("C2:C" & nda)

and the same for the 2nd loop through col A

Cheers Fluff, its working great now.

 

[Fluff]

You're welcome & thanks for the feedback.