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

[totallynotabot]

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.

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

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

Thanks you for your help in advance.

 

[pjmorris]

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.