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