How to hide rows based on cell color in Excel?
In Excel worksheet, I have a list of cells which have many different colors, now, I need to hide all light blue colored rows as following screenshot shown, how could you deal with this task in Excel quickly and easily?
Hide rows based on cell color with VBA code
To hide rows based on a specific background color, the following VBA code may help you, please do as this:
1. Hold down the ALT + F11 keys, then it opens the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module Window.
VBA code: Hide rows based on cell color:
Sub Hidebycolor()
'Updateby Extendoffice
Dim xRg As Range
Dim xTxt As String
Dim xCell As Range
Dim I As Long
On Error Resume Next
If ActiveWindow.RangeSelection.Count > 1 Then
xTxt = ActiveWindow.RangeSelection.Columns(1).AddressLocal
Else
xTxt = ActiveSheet.UsedRange.Columns(1).AddressLocal
End If
LInput:
Set xRg = Application.InputBox("Range:", "Kutools for Excel", xTxt, , , , , 8)
If xRg Is Nothing Then Exit Sub
If xRg.Areas.Count > 1 Then
MsgBox "does not support multiple selections", vbInformation, "Kutools for Excel"
GoTo LInput
End If
If xRg.Columns.Count > 1 Then
MsgBox "the selection is not more than 1 column", vbInformation, "Kutools for Excel"
GoTo LInput
End If
On Error Resume Next
Application.ScreenUpdating = False
For I = 1 To xRg.Count
If xRg.Range("A" & I).Interior.ColorIndex = 20 Then
xRg.Range("A" & I).EntireRow.Hidden = True
End If
Next 'i
Application.ScreenUpdating = True
End Sub3. Then press F5 key to run this code, and a prompt box is popped out to remind you to select the cells that you want to use, see screenshot:
4. And then click OK button, all the light blue colored cells have been hidden at once, see screenshot:
Notes:
1. In the above code, the number 20 within this script If xRg.Range("A" & I).Interior.ColorIndex = 20 Then is the color index number, you can change it as you need.
2. If you are not sure the color index number, you should apply the following code to get the color index which you want to know:
Function GetColor(r As Range) As Integer
GetColor = r.Interior.ColorIndex
End Function
And then enter the formula: =GetColor(A2) to return the specific color index number of the background in cell A2, you change A2 to others as you need.
Hide rows based on cell color with Kutools for Excel
If you have Kutools for Excel, with its Select Cells with Format utility, you can select the specific colored cells first, and then hide them at once.
After installing Kutools for Excel, please do as follows:
1. Click Kutools > Select > Select Cells with Format, see screenshot:
2. In the Select Cells with Format dialog box, do the below operations:
(1.) Click 
button to select the data range that you want to use;
(2.) Then click Choose Format From Cell button to select the specific colored cell that you want to hide rows based on;
(3.) All attributes of the specific cell have been listed into the list box, please just check Background Color under the Fill section.
3. Then click Ok button, all cells with the specific color have been selected at once, and then click Home > Format > Hide & Unhide > Hide Rows, see screenshot:
4. And then the selected cells with the specific color are hidden, see screenshot:
Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now
Demo: Hide rows based on cell color with Kutools for Excel
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite ā Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license ā set up in minutes (MSI-ready)
- Works better together ā streamlined productivity across Office apps
- 30-day full-featured trial ā no registration, no credit card
- Best value ā save vs buying individual add-in