compare columns for duplicates

Snabelhund

New Member
Joined
Nov 11, 2021
Messages
20
Office Version
  1. 2016
Platform
  1. Windows
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.


VBA 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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,214
Office Version
  1. 365
Platform
  1. Windows
Try removing this line
VBA Code:
        Exit Sub
 

Snabelhund

New Member
Joined
Nov 11, 2021
Messages
20
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,214
Office Version
  1. 365
Platform
  1. Windows
You also have the last row variables the wrong way round. This line should be ndb
VBA Code:
For Each c In Range("C2:C" & nda)
and the same for the 2nd loop through col A
 
Solution

Snabelhund

New Member
Joined
Nov 11, 2021
Messages
20
Office Version
  1. 2016
Platform
  1. Windows
You also have the last row variables the wrong way round. This line should be ndb
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,214
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,044
Messages
5,835,103
Members
430,342
Latest member
sdelan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top