2

For example, there are 100(row) x 20(column) cells in a sheet, and every single cell has 5 lines. Some lines are in red, some are marked with strike-through, etc. How can I search all lines in red? How can I find all lines with strike-through? How can I search only by SearchFormat?

I've tried the Excel find with format, but it doesn't work for part of a cell. If all 5 lines are in red, it can find them; but if only 2 lines in red and other 3 lines are normal, it can't find the 2 lines, even when match entire cell contents is not checked.

4
  • 2
    Is the match entire cell contents box ticked?
    – soandos
    Feb 7, 2012 at 7:40
  • This is most likely going to need to be a macro. I'm looking into it. What is it that you want to do when you find these substrings? Highlight the cell? Move them to somewhere else? reformat them? Feb 7, 2012 at 11:39
  • @soandos, no, I didn't check the 'match entire cell contents'
    – Landy
    Feb 9, 2012 at 2:45
  • @Raystafarian, when I find these substring, I had to review them one by one. some will be removed, some will be reformatted. I can do this one by one if Excel can search them all out.
    – Landy
    Feb 9, 2012 at 2:46

1 Answer 1

3

You will need a VBA solution to this:

Here's a sample macro to get you started, thats searches the active sheet (starting at one cell to the right of the active cell) for cells containing text constants, then searches the text in the cell one character at a time for Red. It stops and selects the first cell it finds that contains some red text

Sub FindColorInCells()
    Dim n As Long
    Dim i As Long, j As Long
    Dim cl As Range
    Dim r As Range

    Set r = ActiveSheet.UsedRange
    i = ActiveCell.Row - r.Row + 1
    If i > r.Rows.Count Or i < 0 Then i = 1
    j = ActiveCell.Column - r.Column + 1
    If j > r.Columns.Count Or j < 0 Then j = 1
    i = (r.Columns.Count * (i - 1) + j) Mod r.Cells.Count + 1
    j = r.Cells.Count
    Do While i <> j
        With r(i)
            If Not .HasFormula Then
                If Len(.Value) > 0 Then
                    If TypeName(.Value) = "String" Then
                        For n = 1 To .Characters.Count
                            If .Characters(n, 1).Font.ColorIndex = 3 Then
                                ' found red
                                .Select
                                Exit Sub
                            End If
                        Next
                    End If
                End If
            End If
        End With
        i = i Mod j + 1
    Loop
End Sub
1
  • Tested and works, for colors. Feb 7, 2012 at 12:45

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .