Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

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

  1. #1
    Guest

    Default

    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
    Do
    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
    Posts
    255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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()


    Worksheets(1).Activate
    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
    Else
    ActiveCell.EntireRow.Hidden = True
    End If

    ActiveCell.Offset(1, 0).Select

    Loop

    End Sub


    Good luck!

    Dave



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
  •