compare two columns with B contain A (excel 97)

Topic

New Reply

Hi
I have two set of data in column A and B. in column A it has short form text, in B it has long form text.
how can I compare the text in A against B containing the same text in A and highlight it.
for example
column A column B
123 st123er A1 should be highlighted
a23c sefa23c ver A2 should be highlighted
45 wrse23 A3 should not be highlighted
123 rwet A4 should be highlighted
please reference the code below for my exact compare

Sub Comp_highlight()

Dim OrgRg
Dim ToCompRg
Dim x As Integer
Dim oCell
Dim cCell

‘set orginal range to compare
Range(“A1”).Select
Set OrgRg = Range(ActiveCell, ActiveCell.End(xlDown))
‘Reset Colors
OrgRg.Interior.ColorIndex = xlNone
OrgRg.Font.ColorIndex = 0
‘set other data to compare range
Range(“B1”).Activate
Set ToCompRg = Range(ActiveCell, ActiveCell.End(xlDown))
Application.ScreenUpdating = False
‘Compare NOW
For Each oCell In OrgRg
For Each cCell In ToCompRg
If oCell = cCell Then ‘how to change the code something like if cCell “contain” oCell Then
‘MsgBox oCell.Text & “:” & cCell
With oCell.Interior
.ColorIndex = 15 ‘grey
.Pattern = xlSolid
End With
End If
Next cCell
Next oCell
Application.ScreenUpdating = True
MsgBox “Completed comparison”
End Sub

Reply | Quote

Replies

Joe, I don’t see why the 4th example would be highlighted, I don’t understand what matches.

Also (subject to my first question), you can avoid code and use conditional formatting for column B such as this:

=FIND(A1,B1)

and set the met conditions with gray background etc.

Reply | Quote

I would agrre with JohnBF that conditionall formatting would seem the best approach. Just make sure you base the condition on Formula Is rather than Value Is. See attached.

If you want some code, tryy the following :

    Dim oCell As Range
    For Each oCell In Range([A1], [A1].End(xlDown))
        If InStr(oCell.Offset(0, 1), oCell) > 0 Then
            oCell.Offset(0, 1).Interior.ColorIndex = 15
        End If
    Next

Andrew C

Reply | Quote

I echo John’s question/solution and I think that you would want to do is the other way around, ie, highlight the portion of the long string that contains the short. Here are two quickies that may help. If not, please post back. –Sam

Option Explicit

Sub HyLite1()
Dim c As Range
    For Each c In Range(Cells(1, 1), Cells(1, 1).End(xlDown))
        If InStr(c.Offset(0, 1), c) > 0 Then
            c.Font.ColorIndex = 3
        Else
            c.Font.ColorIndex = xlColorIndexAutomatic
        End If
    Next c
End Sub

Sub HyLite2()
Dim c As Range
Dim iStart As Integer
    For Each c In Range(Cells(1, 1), Cells(1, 1).End(xlDown))
        iStart = InStr(c.Offset(0, 1), c)
        If iStart > 0 Then
            c.Offset(0, 1).Characters( _
                Start:=iStart, Length:=Len©).Font.ColorIndex = 3
        Else
            c.Offset(0, 1).Font.ColorIndex = xlColorIndexAutomatic
        End If
    Next c
End Sub

Reply | Quote

Thank you all for the help.
Both codes solve my problem.
thanks

Reply | Quote