Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Search for coloured text (E97 SR2)

    I have been issued with a revised spreadsheet. The revised text is coloured red, although some is coloured yellow. Sometimes the cell colour has been changed instead of the text colour. Can someone please suggest a code fragment I can use to find these changes (there are many sheets on the spreadsheet, I can make the code search multiple sheets..)

    Thanks in advance,
    Marty

    The word "color" included so other people can search for this post (I learnt a different version of English down here).

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search for coloured text (E97 SR2)

    Something like this:

    Sub FindColoredcells()
    Dim oSheet As Worksheet
    Dim oCell As Range
    Dim vAnswer As Variant
    For Each oSheet In ActiveWorkbook.Worksheets
    For Each oCell In oSheet.UsedRange.Cells
    If oCell.Interior.ColorIndex = 4 Or oCell.Font.ColorIndex = 4 Then
    vAnswer = MsgBox("found one at " & oSheet.Name & "!" & oCell.Address & ", find next?", vbYesNo)
    If vAnswer = vbNo Then
    oCell.Parent.Select
    oCell.Select
    Exit Sub
    End If
    End If
    Next
    Next
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  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: Search for coloured text (E97 SR2)

    The following code will 'gather' all cells with either colored text or background into a single range, which can then be manipulated as required.<pre>Sub SelectColorCells()
    Dim ColorRange As Range
    Dim oCell As Range
    Set ColorRange = ActiveCell
    For Each oCell In ActiveSheet.UsedRange
    If oCell.Interior.ColorIndex <> xlNone Or _
    oCell.Font.ColorIndex <> xlAutomatic Then
    Set ColorRange = Union(ColorRange, oCell)
    End If
    Next
    'ColorRange.Select
    End Sub</pre>

    To work it requires that you manually select the first cell that fits the bill, and run the code.

    Andrew C

  4. #4
    Star Lounger
    Join Date
    Feb 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search for coloured text (E97 SR2)

    Many thanks for the replies!! I rolled both together to get a sub which works very well.
    One problem left, however. What about a cell containing several words, one of which has been altered from default? (grr...)

    thanks for the help
    Marty

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search for coloured text (E97 SR2)

    You would have to loop through each character and check the ColorIndex property or the Font object for each character in the cell's Character property.
    Legare Coleman

Posting Permissions

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