Highlight matching parts of a string

I have two cells containing comma seperate strings of text. I would like to compare them and highlight any matches in the string.

Eg
A1= a, b, c, d, e
A2= a, f, e, g, h.

The list are in no particular order or length and can contain any number of values. all seperated by comma space.

You cannot conditionally format a part of a cell entry.

If you separate each entry into individual cells, then you can highlight cells.

I guess I assumed you were looking for conditional formatting via native Excel functions...

Sub snb()
  For Each c In Split([A1], ",")
    if instr([A2],c)>0 then
      With [A2].Characters(InStr([A2], c), Len(c)).Font
        .ColorIndex = 4
        .Bold = True
      End With
    End if
  Next
End Sub

Simple version:

Function StringCompare(r1 As Range, r2 As Range) As Boolean
    Dim varData As Variant
    Dim lngMatch As Long
    Dim i As Integer, bDiff As Boolean
    Dim strTest As String
    strTest = r2.Value & ","
    varData = Split(r1.Value, ",")
 
    For i = LBound(varData) To UBound(varData)
        lngMatch = InStr(1, strTest, Trim(varData(i)) & ",", vbTextCompare)
        If lngMatch > 0 Then
            With r2.Characters(lngMatch, Len(Trim(varData(i)))).Font
                .Bold = True
                .Size = 9
                .Color = RGB(255, 0, 0)
            End With
            bDiff = True
        End If
    Next i
StringCompare = bDiff
End Function

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save