How do i hide rows in Excel based on cell color

There are several ways to hide and unhide columns and rows in Excel. One thing you might not be aware of: you can hide and unhide more than one column or row, at the same time, even if they are non-contiguous. Better yet, if they are contiguous you can use Excel's Grouping tool. Below I have outlined a couple of time-saving ways to hide and unhide your Excel columns and rows.

Hide and Unhide Columns and Rows

1. Select the number of columns or rows you are interested in hiding (use the Control key to select additional noncontiguous columns or rows).
2. Click the Format button located on the Home tab / Cells group then choose Hide Columns or Rows (another option is to Right click on a highlighted column or row heading and select hide).
3. Your Columns and Rows are now hidden.

Unhide all Columns or Rows

1. Click on the Format button located under the Home tab / Cells group then choose Unhide Columns or Rows (another option is to drag over the range of columns or rows you are interested in unhiding then Right click in the column or row heading and choose unhide).
2. Your Columns and Rows are now visible.

Hide and Unhide Columns and Rows Using the Group Feature

The following steps below outline how to use the Group option in Excel for versions 2007, 2010, 2013, and 2016. Grouping only works with Columns and Rows that are contiguous (together).

1. Select the number of contiguous columns or rows you are interested in hiding (you cannot use the Control key during this operation).
2. Click the Group button located on the Data tab / Outline group then choose
   
3. Your Columns and Rows are now hidden.

Not only are your columns or rows hidden but Excel has added show hide buttons to help you toggle your view:

Collapsed view of columns:

To Turn the Group Feature Off

1. Select the range of Columns or Rows you would like to Ungroup.
2. Click the Group button located on the Data tab / Outline group then choose
3. The Grouping feature is now removed.

Did you like this step-by-step tutorial? We've got a great video, 10 Microsoft Excel Aha Features You Need to Know. Watch it completely free!

the easiest way to find out how to do something in VBA is to use the macro recorder,click macro >> record, now do what you lets say color a cells background, so click a colour and click the cell you want and now stop the recorder you will see code like

Code:

Sub Macro1()
' Macro1 Macro
' Macro recorded 16/11/2006 by C Shippey

    Range("A1").Select
    With Selection.Interior
        .ColorIndex = 6
        .Pattern = xlSolid
    End With
End Sub

now try altering it a little because the recorder includes all statements which you might not require the easiest way is the put a ' in front of the line, the line will go green and be ignored when the code is ran, in the above example the code could be changed to

Code:

Sub macro99()
Range("a1").Interior.ColorIndex = 6
End Sub

i would personally never use the WITH ....END WITH if i had a range of cells i would put

Range("A1:C1")

but then im not the best VBA coder anyway

but what you learn from the recorder is the statements that you need in this case

interior.colorindex =

now you can just put your IF statements round it and you are writing code, although this can be used for most tasks its no good for everything, the recoder would create a LOOP everytime you did something it would just add more code but when reading you could delete most of it and include a LOOP

HTH

 

You can quickly filter data based on visual criteria, such as font color, cell color, or icon sets. And you can filter whether you have formatted cells, applied cell styles, or used conditional formatting.

Newer versionsOffice 2011



1. In a range of cells or a table column, click a cell that contains the cell color, font color, or icon that you want to filter by.

2. On the Data tab, click Filter.

3. Click the arrow

   in the column that contains the content that you want to filter.

4. Under Filter, in the By color pop-up menu, select Cell Color, Font Color, or Cell Icon, and then click a color.

   Can you hide rows in Excel based on cell value?

   Excel provides a filter feature that can allow users to easily filter and hide rows based on cell values. To use this feature, select the “Home” tab and look in the “Editing” group for the “Sort and Filter” feature.

   How do I hide rows in Excel based on content?

   Hide unused rows so that only working area is visible.

   Select the row beneath the last row with data (to select the entire row, click on the row header)..

   Press Ctrl + Shift + Down arrow to extend the selection to the bottom of the sheet..

   Press Ctrl + 9 to hide the selected rows..

   How do I hide or unhide rows in Excel based on cell value?

   One way is to use the built-in filter feature. To do this, first select the data that you want to filter. Then, click the Data tab on the ribbon and click the Filter button. In the drop-down menu that appears, click the column that you want to filter by and then uncheck the box next to the value that you want to hide.