# Searching names in a column and coloring them if they match in another column

#### totallynotabot

##### New Member
Hello.
Sorry if my English is bad, I am french. I would like to compare a column of name with another column of names.
I used a levenshtein code to have a percentage of similarity with the columns.
What i want to do is like if the words are matching with a > 70%, it colors the name in green
If it doesn't match at all,( <30%) it colors the name in red. And if it is really close but some letters are missing, ( >30% and <70%) then it is orange.
here is what i did with an engineer student that left the company.
I am very new to vba and don't understand completely what he did.
Here is the levenshtein function we used using with his macro.

VBA Code:
``````Function Levenshtein3(ByVal string1 As String, ByVal string2 As String) As Long

Dim i As Long, j As Long, string1_length As Long, string2_length As Long
Dim distance(0 To 60, 0 To 50) As Long, smStr1(1 To 60) As Long, smStr2(1 To 50) As Long
Dim min1 As Long, min2 As Long, min3 As Long, minmin As Long, MaxL As Long

string1_length = Len(string1):  string2_length = Len(string2)

distance(0, 0) = 0
For i = 1 To string1_length:    distance(i, 0) = i: smStr1(i) = Asc(Mid\$(string1, i, 1)): Next
For j = 1 To string2_length:    distance(0, j) = j: smStr2(j) = Asc(Mid\$(string2, j, 1)): Next
For i = 1 To string1_length
For j = 1 To string2_length
If smStr1(i) = smStr2(j) Then
distance(i, j) = distance(i - 1, j - 1)
Else
min1 = distance(i - 1, j) + 1
min2 = distance(i, j - 1) + 1
min3 = distance(i - 1, j - 1) + 1
If min2 < min1 Then
If min2 < min3 Then minmin = min2 Else minmin = min3
Else
If min1 < min3 Then minmin = min1 Else minmin = min3
End If
distance(i, j) = minmin
End If
Next
Next

' Levenshtein3 va retourner un pourcentage (100%=exacte) basé sur les similaritées et longueurs etc...
MaxL = string1_length: If string2_length > MaxL Then MaxL = string2_length
Levenshtein3 = 100 - CLng((distance(string1_length, string2_length) * 100) / MaxL)

End Function``````

VBA Code:
``````Sub test()
Workbooks("FinTer_012019_KTS_fichiers 12.xlsx").Activate
Sheets("SanctionsFile").Select
list_lines = Cells(Rows.Count, 1).End(xlUp).Row
Workbooks("FinTer_012019Cie AZEC.xlsx").Activate
Sheets("Liste nationale").Select
terro_lines = Cells(Rows.Count, 1).End(xlUp).Row

Workbooks("FinTer_012019_KTS_fichiers 12.xlsx").Activate
Sheets("SanctionsFile").Select
For i = 2 To list_lines
Debug.Print i
cur_max = 0
cur_name = Range("D" & i).Value & " "
If Range("E" & i).Value <> "n/a" Then
cur_name = cur_name & Range("E" & i).Value
End If
For j = 2 To terro_lines
nat_name = Workbooks("FinTer_012019Cie AZEC.xlsx").Sheets("Liste nationale").Range("B" & j).Value & " " & Workbooks("FinTer_012019Cie AZEC.xlsx").Sheets("Liste nationale").Range("C" & j).Value
leven = Levenshtein3(Range("H" & i).Value, Range("D" & j).Value)
cur_max = WorksheetFunction.Max(leven, cur_max)
Next j
If cur_max >= 70 Then
Range("D" & i).Select
Selection.Interior.Color = 255
Range("E" & i).Select
Selection.Interior.Color = 255
End If
Next i

End Sub``````

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

#### pjmorris

##### Well-known Member
I assume your function returns a figure between 0-100% that depends on your criteria. Assuming the function works as you want it to then I would use it in a Conditional Format statement that compares its return value to the levels you require. Let me know if you need more help and some sample data would be helpful.

Replies
4
Views
140
Replies
4
Views
177
Replies
2
Views
245
Replies
3
Views
141
Replies
2
Views
155

1,127,795
Messages
5,626,926
Members
416,209
Latest member
tan21

### 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.

### Which adblocker are you using?

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

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