Selecting a row based on it's cell color
Find bottlenecks in your Excel workbooks
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Selecting a row based on it's cell color

  1. #1


    I currently have a macro that runs through the spreadsheet and highlights all rows yellow that have a particular value in column C. Let's say the value is "5". Sometimes this value is the only one in the cell, and sometimes it is part of a list (i.e. "1,2,3,4,5,6"). These list rows are also highlighted. What I can't figure out how to do is to hide all of the rows that are NOT highlighted by the above macro. I haveplaced the code below. Thanks in advance...

    Sub search()
    With Worksheets(1).Range("c1:c6345")
    Set c = .Find("04680-00", LookIn:=xlValues)
    If Not c Is Nothing Then
    firstAddress = c.Address
    c.Interior.Pattern = xlPatternWhite50
    c.EntireRow.Interior.ColorIndex = 2
    Set c = .FindNext(c)
    Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
    End With
    End Sub

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Post Thanks / Like
    0 Post(s)
    0 Thread(s)


    I've never been a big fan of the find method because it fails often and when it fails the programs crashes. Instead I would do this:

    Sub search()

    Cells(1, 3).Select

    Do Until ActiveCell.Row = 500 '(any criteria that makes the search stop works here)

    If InStr(1, ActiveCell.Value, "04680-00") <> 0 Then
    With ActiveCell
    .Interior.Pattern = xlPatternLightUp
    (whatever pattern you used didn't work on my version of excel)

    .EntireRow.Interior.ColorIndex = 2
    End With
    ActiveCell.EntireRow.Hidden = True
    End If

    ActiveCell.Offset(1, 0).Select


    End Sub

    Good luck!


User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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