Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Feb 2002
    Location
    US
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    compare two columns with B contain A (excel 97)

    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

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: compare two columns with B contain A (excel 97)

    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.
    -John ... I float in liquid gardens
    UTC -7▒DS

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: compare two columns with B contain A (excel 97)

    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 :<pre> 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</pre>

    Andrew C
    Attached Images Attached Images

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: compare two columns with B contain A (excel 97)

    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
    <pre>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</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. #5
    Lounger
    Join Date
    Feb 2002
    Location
    US
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: compare two columns with B contain A (excel 97)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •