Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Aug 2003
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    find second text string (excel 2003)

    Hi all
    I have lines in a macro, it can find the oCell in cCell only once, and format to color 3
    istart = InStr(cCell, oCell)
    If InStr(cCell, oCell) > 0 Then
    cCell.Characters(Start:=istart, Length:=Len(oCell)).Font.ColorIndex = 3

    If the cCell contains oCell 2 or more times, how can I add more line to find and format them?
    something like that
    cCell.Characters(Second:=istart?, Length:=Len(oCell)).Font.ColorIndex = 4
    thanks
    cCell oCell
    124 4512400000012400124

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: find second text string (excel 2003)

    This will turn the first to colorindex = 3, the second to 4, I wasn't sure of your pattern so I just continued to 5,6,7, etc

    <pre>Option Explicit
    Sub ColorAllFound()
    Dim oCell As Range
    Dim cCell As Range
    Dim iLenOC As Integer
    Dim iLenCC As Integer
    Dim iCount As Integer
    Dim iStart As Integer
    Dim iLook As Integer
    Dim x As Integer
    Dim AWF As WorksheetFunction

    Set cCell = Range("A1")
    Set oCell = Range("a2")
    Set AWF = Application.WorksheetFunction

    iLenOC = Len(oCell)
    iLenCC = Len(cCell)
    iCount = (iLenOC - Len(AWF.Substitute(oCell, cCell, ""))) / iLenCC
    iLook = 0
    For x = 1 To iCount
    iStart = InStr(iLook + 1, oCell, cCell)
    oCell.Characters(start:=iStart, Length:=iLenCC).Font.ColorIndex = x+2
    iLook = iStart + iLenCC
    Next
    End Sub</pre>


    Instead of "defining" the oCell and cCell, you could have the code get them in a loop if desired, but you weren't specific

    Steve

  3. #3
    Star Lounger
    Join Date
    Aug 2003
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: find second text string (excel 2003)

    Steve:
    I am sorry for the confusion. My original macro is this.
    How can I modify this to achive the same results

    Sub compare_b_contain_a()
    '
    ' Xmbtr Macro
    ' Macro recorded 6/8/1999 by PC Setup
    '

    '
    Dim OrgRg
    Dim ToCompRg
    Dim x As Integer
    Dim oCell
    Dim cCell
    Dim istart As Integer 'delete
    '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
    istart = InStr(cCell, oCell) 'delete
    If InStr(cCell, oCell) > 0 Then
    cCell.Characters(Start:=istart, Length:=Len(oCell)).Font.ColorIndex = 3
    '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

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: find second text string (excel 2003)

    Like this?
    Steve
    <pre>Option Explicit
    Sub compare_b_contain_a()
    Dim OrgRg
    Dim ToCompRg
    Dim x As Integer
    Dim oCell As Range
    Dim cCell As Range
    Dim iLenOC As Integer
    Dim iLenCC As Integer
    Dim iCount As Integer
    Dim iStart As Integer
    Dim iLook As Integer
    Dim AWF As WorksheetFunction

    Set AWF = Application.WorksheetFunction

    'set orginal range to compare
    Set OrgRg = Range(Range("A1"), Range("A1").End(xlDown))

    'set other data to compare range
    Set ToCompRg = Range(Range("B1"), Range("B1").End(xlDown))
    Application.ScreenUpdating = False

    'Reset Colors
    OrgRg.Interior.ColorIndex = xlNone
    OrgRg.Font.ColorIndex = 0
    ToCompRg.Interior.ColorIndex = xlNone
    ToCompRg.Font.ColorIndex = 0

    'Compare NOW
    For Each oCell In OrgRg
    For Each cCell In ToCompRg
    iLenOC = Len(oCell)
    iLenCC = Len(cCell)
    iCount = (iLenCC - Len(AWF.Substitute(cCell, oCell, ""))) / iLenOC
    iLook = 0
    For x = 1 To iCount
    iStart = InStr(iLook + 1, cCell, oCell)
    cCell.Characters(start:=iStart, Length:=iLenOC).Font.ColorIndex = x + 2
    iLook = iStart + iLenOC
    Next
    With oCell.Interior
    .ColorIndex = 15 'grey
    .Pattern = xlSolid
    End With
    Next
    Next
    Application.ScreenUpdating = True
    MsgBox "Completed comparison"
    End Sub</pre>


  5. #5
    Star Lounger
    Join Date
    Aug 2003
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: find second text string (excel 2003)

    o ye
    works
    thanks, Steve.

  6. #6
    Star Lounger
    Join Date
    Aug 2003
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: find second text string (excel 2003)

    two problems.
    1) it can not find second text string if they are next together.
    like : add 1241240000411. can not find second set of 124.

    2) it found the wrong text and format it right on the first few letters: add
    any idea?

  7. #7
    Star Lounger
    Join Date
    Aug 2003
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: find second text string (excel 2003)

    I changed to
    iLook = iStart -1+ iLenOC
    instead of iLook = iStart + iLenOC
    now is fine.
    thanks

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: find second text string (excel 2003)

    1) <img src=/S/blush.gif border=0 alt=blush width=15 height=15> glad you figured it out on your own.

    2) I don't understand, could you give an example of what is was searching in and what the searchstring was when it fails or gives an incorrect formatting.

    Steve

Posting Permissions

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