News, tips, advice, support for Windows, Office, PCs & more. Tech help. No bull. We're community supported by donations from our Plus Members, and proud of it
Home icon Home icon Home icon Email icon RSS icon
  • Filter Rows That Have At Least One Cell with Background Other Than White

    Home Forums AskWoody support Productivity software by function MS Excel and spreadsheet help Filter Rows That Have At Least One Cell with Background Other Than White

    This topic contains 2 replies, has 2 voices, and was last updated by  notbrl 6 days, 14 hours ago.

    • Author
      Posts
    • #1907044 Reply

      notbrl
      AskWoody Lounger

      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.

    • #1907166 Reply

      zeddy
      AskWoody_MVP

      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 6 days, 17 hours ago by  zeddy.
      • This reply was modified 6 days, 17 hours ago by  zeddy.
      • This reply was modified 6 days, 17 hours ago by  zeddy.
      1 user thanked author for this post.
    • #1907210 Reply

      notbrl
      AskWoody Lounger

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

    Please follow the -Lounge Rules- no personal attacks, no swearing, and politics/religion are relegated to the Rants forum.

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

    You can use BBCodes to format your content.
    Your account can't use Advanced BBCodes, they will be stripped before saving.