Mark letters that differ between two cells in Excel

Question

Given two cells, each containing a string of length n (i.e. 11), I want to mark all letters in the bottom cell in bold, red that differ from the cell at the top.

Here is a toy example:

Why is this useful? I need to compare many amino acid sequences with each other, and this would be a great way to visualize the results in Excel for biologists.

Answer 1

Following function compares two strings, assuming 'rngWord1' as the original string, and 'rngWord2' the one to change:

Sub comparetwostrings(rngWord1 As Excel.Range, rngWord2 As Excel.Range)
    Dim l As Long

    If rngWord1.Value <> rngWord2.Value Then

        For l = 1 To Len(rngWord1.Value)   

            If Mid(rngWord1.Value, l, 1) <> Mid(rngWord2.Value, l, 1) Then
                rngWord2.Characters(l, 1).Font.Color = vbRed
                rngWord2.Characters(l, 1).Font.Bold = True    
            Else 
                rngWord2.Characters(l, 1).Font.Color = vbBlack   
            End If

        Next l

    End If
End Sub

To run the function, a Command button called CompareAllStrings can be created. Inside, a range is defined to loop over, and perform pairwise string comparisons. Both 'rng1' and 'Offset' should be adjusted based on the specific Excel layout:

Private Sub btnCompareAllStrings_Click()
    Dim rng1 As Range, e As Range

    Set rng1 = Range("B2:Z2")
    For Each e In rng1
        comparetwostrings e, e.Offset(1, 0)
        comparetwostrings e.Offset(2, 0), e.Offset(3, 0)
    Next
End Sub