Comparing multiple columns for list of items

[Closed]
Report
Posts
1
Registration date
Tuesday November 19, 2013
Status
Member
Last seen
November 19, 2013
-
Posts
2809
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 23, 2021
-
Dear experts

I have list of names with multiple words in column A

List of names with multiple words in column B

List of names with multiple words in column C

In Column D, I have a list of names which Needs to be compared if they exist in either A, B or C. If so highlight them with Yellow or Red.

Because the list in Column D have some names changed/spell changed/Deleted.. they should not exist any more.
Note: even a single letter difference like some names have added/deleted (s) in the end have to be filtered out. Please can you help me.
Note 2: In some searches I have only column to compare and some have 2 and some I have 3 columns .. Please appreciate your help.

3 replies

Posts
2809
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 23, 2021
484
Hi Protocoder,

The following code will loop through column D.
When a partial match is found in the pre set range the cell from column D will turn red.

Here is the code:
Sub RunMe()
Dim lRow As Integer
lRow = Range("D1").End(xlDown).Row

For Each cell In Range("D1:D" & lRow)
With Range("A1:C9") 'Make sure this range is conform yours
Set c = .Find(cell, LookIn:=xlValues)
If Not c Is Nothing Then cell.Interior.ColorIndex = 3
End With
Next cell

End Sub

Best regards,
Trowa
Thank you Trowa.

I have read and also executed. I must apologize for the confusion.
There must be exact match not a letter more or a letter less. The reason being in the new version there has been changes in the names and basically testing when I run the report, all historical and new reports point to the changed name not the old name. Thank you for your time.
Posts
2809
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 23, 2021
484
Hi Protocoder,

Then you will have to change the following line:
Set c = .Find(cell, LookIn:=xlValues)
into:
Set c = .Find(cell, LookIn:=xlValues, lookat:=xlWhole)

Best regards,
Trowa