Filter out rows with ANY cell that is filled with a color

[ashleylabreck]

Hi!

I have a report that I look through each day that is over 2000 lines... It seems like I have figured out a simple solution to make it easier: I only need to look and any row that has a cell filled yellow.. basically finding the cells with errors.

Is that something simple to do?

Any help would be great... Again, I just need excel to hide any row that does not have a cell with a fill color... or to have it bring those with a fill to the top....

Let me know if you have any questions!

Thanks!

 

[mumps]

Are the cells with a fill color in any particular column or can they be anywhere on the worksheet?

 

[ashleylabreck]

They can be anywhere in the worksheet... That's what made it hard for me to figure out. They could be in any of the columns.

 

[Jonmo1]

Using Cell color for any "function" is generally not advised.
Cell coloring is pretty much just to make the book visually appealing.
But to try to extract data based on cell color is very difficult.

You'd be better of creating a column where you could put an X or something in that column for the rows you want to hide.
Then you can use the AutoFilter on that column.


Or is there some other criteria that can be used?
I mean, what caught your eye on each cell that made you choose to color it yellow?
Was it a number that was too high or too low?
Was it a text string that contained certain key words?

Stuff like that can be used.

 

[ashleylabreck]

I actually don't color the cells. I have a report that I open that is generated by our system. It colors the cells yellow that need attention, either they are missing information, or they have wrong information. Then I go through the 2000 some rows and address any cell that is yellow in a row.

I know it's not a great system, but it is generated that way, not by excel, by an outside program.

 

[Jonmo1]

Which version of XL are you using?
How many columns are there?
Of the cells that are NOT yellow, are they colored some other color? Or do they have the "No Fill" option?

 

[ashleylabreck]

I am using Excel 2010...

There are Columns A - S (19 columns)

There are some other colors, the report separates the sections of data with blue rows, and there are some intermittent red cells as well for other data.

 

[Jonmo1]

Well, you can use the Autofilter to filter by color.
But you'd have to do 1 column at a time though.

Filter column A for Yellow, do whatever you need to do to address the problem, then unfilter column A
Filter column B for Yellow, do whatever you need to do to address the problem, then unfilter column B
Etc..

I know that's not ideal, but again...Using Cell "Color" for any functionality other than appearance is not ideal in XL.

 

[ashleylabreck]

HAHA, yea I hear ya, we have such great systems... but that is actually not a terrible idea.. I might see some duplicates, ie, if a row has more than one yellow cell.. but it would be better than what I'm doing now....

You know.. I did a Find and selected all the yellow cells.. and it shows those rows highlighted.. is there anyway to capture those rows? I tried just a regular Copy but it says "Can't be used on multiple selections"

 

[Jonmo1]

If you want to use a Macro, This is a very basic one...
It will put "Yellow" in column S for every cell that is yellow (colorindex 6)
Change S to any available column you like
And you may need to do a trial and error to get the correct colorindex #
Then after the macro runs, you can filter on column S

Sub test()
For Each c In ActiveSheet.UsedRange
    If c.Interior.ColorIndex = 6 Then
        Cells(c.Row, "S").Value = "Yellow"
    End If
Next c
End Sub