Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
2  
Is the match entire cell contents box ticked? –  soandos Feb 7 '12 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? –  Raystafarian Feb 7 '12 at 11:39
    
@soandos, no, I didn't check the 'match entire cell contents' –  Landy Feb 9 '12 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 '12 at 2:46

1 Answer 1

up vote 3 down vote accepted

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
share|improve this answer
    
Tested and works, for colors. –  Raystafarian Feb 7 '12 at 12:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.