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!


Some videos you may like

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