Selecting a row based on it's cell color

G

Guest

Guest
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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top