CODEWITHSUNDEEP
vbaexcel-formula
R Drago
R Drago

Reputation: 11

Need to see what is different text between two cells

In Excel I have

I need to make a Column C that shows the difference between the two.

For example, highlight 1234 and 7 as a different font color. I'm open for any ideas on how to do it.

Upvotes: 1

Views: 830

Answers (2)

R Drago
R Drago

Reputation: 11

Sub CompareInColor()

ActiveSheet.Range("C1").Value = ActiveSheet.Range("A1").Value

For i = 1 To Len(ActiveSheet.Range("A1").Value)
    If (ActiveSheet.Range("A1").Characters(i, 1).Text <> ActiveSheet.Range("B1").Characters(i, 1).Text) Then
        ActiveSheet.Range("C1").Characters(i, 1).Font.Color = RGB(255, 0, 0)
End If
Next i

End Sub

Sub CompareInColorFlip()

ActiveSheet.Range("D1").Value = ActiveSheet.Range("B1").Value

For i = 1 To Len(ActiveSheet.Range("B1").Value)
    If (ActiveSheet.Range("B1").Characters(i, 1).Text <> ActiveSheet.Range("A1").Characters(i, 1).Text) Then
        ActiveSheet.Range("D1").Characters(i, 1).Font.Color = RGB(255, 0, 0)
End If
Next i

End Sub

This compares A1 and B1 only... Loop through your rows if you have many of them. Also, I assumed that the length of A1 and A2 is the same, otherwise an out-of-range index error may evolve.

Upvotes: 0

ƬƦƖƝƛ
ƬƦƖƝƛ

Reputation: 179

This should do the trick:

Sub CompareCells(c1 As Range, c2 As Range)
    Dim p As Long
    If c1.Cells.Count + c2.Cells.Count <> 2 Then _
        MsgBox "Must specify two single cells.": Exit Sub
    For p = 1 To IIf(Len(c2) < Len(c1), Len(c2), Len(c1))
        If Mid(c1, p, 1) <> Mid(c2, p, 1) Then c2.Characters(p, 1).Font.Color = vbRed
    Next p
End Sub

If your values are in cells A1 and A2, you could use it like this:

CompareCells [a1], [a2]

screenshot

Upvotes: 5

Related Questions