Filter Rows That Have At Least One Cell with Background Other Than White

Topic

New Reply

I apologize in advance that I have not included the workbook or a screenshot of the workbook.  Due to the sensitive nature of the information I am not able to show this data.

I have a single sheet table (report) that consists of over 20,000 rows across 18 columns.  The vast majority of cells have the default white background.  Any engineering changes to the information from the previous report are indicated by a background color of yellow.

The change in data could be a single cell in a row, multiple cells in a row, or an entire row.  I need to filter the table to show only rows where any cell(s) have had engineering changes, or a yellow background.

I have tried to Filter by Color, and tried GET.CELL(63,OFFSET(INDIRECT neither of which result in what we are looking to do.

From a post I read I am testing using a 19th column where a formula will return TRUE if any of the cells in that row has a background other than white.  Then I can filter the table to show only cells with a value of TRUE.

I can’t for the life of me figure out the IF formula to test for cell background color.  Is this even possible?  My hair can’t get much grayer…

If push comes to shove, I can use VBA but would prefer to do this with a formula instead.

Thanks in advance for reviewing my question and for any guidance that may help me on my way.

Reply | Quote

Replies

[zeddy]

Hi

How about not using any vba and not using any formulas?

1. Start on your data sheet, scroll to the first row and select cell [A1]
2. To show only rows that contain any yellow cell(s) anywhere in the data rows, make sure your heading row also has a cell that is yellow (e.g make cell [A1] yellow background)
3. Press [Ctrl][a] ;this selects the entire ‘region’ i.e. all records
4. Press [Ctrl][9] ;this hides all rows initially
5. Press [Ctrl][f] ; this displays the Find and Replace dialog
6. Press [Alt][t] to display the Find and Replace [Options] (if not already show)
7. Press [Alt][m] to display the Format options
8. click the [Fill] tab and select yellow as the Background Color for the search and click [OK]
9. Press [Alt][ i] (or click the [Find All] button)
10. Press [Ctrl][a] ; selects all the found items
11. press [esc] ; to cancel the Find and Replace dialog (note: cells are still selected)
12. Press [Ctrl][Shift][9] ; to unhide rows of the selected found cells
13. Press [Ctrl][Home] to scroll to top of sheet.

Done.

zeddy
Excel Drill-Down Rigger

• This reply was modified 5 years, 9 months ago by zeddy.
• This reply was modified 5 years, 9 months ago by zeddy.
• This reply was modified 5 years, 9 months ago by zeddy.

1 user thanked author for this post.

notbrl

Reply | Quote

Thank you Zeddy!  Worked like a charm.  Did exactly what was needed.

Reply | Quote