While working in Excel, users might need to hide cells for work purposes. Among the hidden cells, some may be highlighted or be a different color. To hide those highlighted cells in Excel is the main topic of our article. By using what or which procedure, we can accomplish our task. In this article, we will show you how to hide highlighted cells in Excel.
How to Hide Highlighted Cells in Excel: 2 Ways
To describe our article, we will use the following data set. Here, we have a data table containing seven rows and three columns. In our data set, we have some random student’s name, their grade percentage, and their attendance percentage. In the Student Name column, we have highlighted each cell in different colors. We will apply two different approaches to hide those cells. In our first method, we will use the Filter feature of Excel to hide those highlighted cells, and in our second approach, we will apply VBA.
1. Filtering by Selected Cell Color to Hide Highlighted Cells in Excel
In the first method of our article, we will filter those cells according to their color and then hide them from the data table. See the following steps for a better understanding.
Step 1:
- First of all, we will create a helper column in column E.
Step 2:
- Secondly, we will write down the colors of each cell from column B in our helper column.
Step 3:
- Thirdly, select the whole helper column and then go to the Data tab of the ribbon.
- From there, go to the Filter command under the Sort & Filter group.
Step 4:
- As a result, this command will allow you to filter the data range from B4:B10 based on their color.
- First of all, press the drop-down button in the helper column.
- Then, you will see all the filter options from the drop-down.
- Thirdly, under the Select All command you will notice all the cell colors as options in there.
- From there, unmark any cell color, and after unmarking, you will not see the cell in the data set.
- For example, we will choose the color Yellow.
Step 5:
- Finally, you will see that cell B6 in row 6 containing the yellow color is hidden from the worksheet.
Step 6:
- Furthermore, if you want to show the hidden cell, go to the filter drop-down again and mark the specific cell color.
Step 7:
- Finally, you will see the highlighted cell with its corresponding row again.
Read More: How to Hide Confidential Data in Excel
2. Applying VBA to Hide Highlighted Cells in Excel
In our second method, we will apply VBA code to hide the highlighted cells. We will apply VBA based on two different criteria.
2.1 Inserting RGB Values
In this approach, we will hide the highlighted cells based on their RGB value through VBA. Now go through the following steps to understand the procedure.
Step 1:
- First of all, take the following data set for our procedure.
- Then, select any highlighted cell from column B to determine its RGB value.
- For example, we will find out the RGB value of the color yellow which is in cell B6.
Step 2:
- Secondly, go to the Home tab of the ribbon.
- Then choose the little arrow on the lower right side of the Font group.
Step 3:
- Thirdly, a dialogue box named Format Cells will appear.
- From that box go to the Fill tab.
- Then, choose More Colors….
Step 4:
- Fourthly, another dialogue box named Colors will appear.
- Then go to the Custom tab and you will find the RGB value for yellow which is 255,255,0.
Step 5:
- In this step, we will insert the VBA code.
- To do that, go to the Developer tab of the ribbon and select the Visual Basic command.
Step 6:
- After selecting, you will see the VBA window.
- Then, choose the Module command from the Insert tab.
Step 7:
- Then, copy the following VBA code into the module.
'Set the function name
Sub Hide_Rows_by_Inserting_RGB_Values()
'Declare the row till which the data remains
Dim EndRow As Long
'Declare the counter variable
Dim y As Long
'Set the rows property and count all the available rows
'Set the column number in which the highlighted cell is
EndRow = Cells(Rows.Count, 2).End(xlUp).Row
'Scan each row in column B and check the cell is yellow by for looping
For y = 4 To EndRow
'Set the cells property and looking for interior color throguh RGB function
If Cells(y, 2).Interior.Color = RGB(255, 255, 0) Then
'Set what happens after the condition is met
Rows(y).Hidden = True
'End the If function
End If
'Repeat the steps to complete until last row appears
Next y
End Sub
VBA Code Breakdown
- The function name is Hide_Rows_by_Inserting_RGB_Values.
- The variable names are EndRow and y and they are long type variables.
- EndRow = Cells(Rows.Count, 2).End(xlUp).Row: We set the row property and count all available rows and also set the column number in which the highlighted cell is.
- For y = 4 To EndRow: We will use this for looping to check each row in column B staring from cell B4 to find the yellow cell color.
- If Cells(y, 2).Interior.Color = RGB(255, 255, 0)Rows(y).Hidden = True: With the IF function we will look for the color according to their RGB value which is 255,255,0 for yellow. If the condition is met then this code will hide the entire row.
- End If: The end of the IF function.
- Next y: We will continue to look through the last row to meet the condition.
Step 8:
- After pasting the code, save it and press the play button or F5 to run the code.
Step 9:
- Finally, after playing the code you will see that the B6 cell containing the yellow color has been hidden from the data table.
2.2 Formatting Interior Color Index
In addition to the above method, you can apply another kind of VBA code to hide the highlighted cells in Excel. This VBA requires the color index number in the code for hiding the cells. Follow the following steps for a detailed view of the procedure.
Step 1:
- First of all, we will use the following data set for our procedure.
- In addition, we have to create another data table under the main data set where we will find out the color index number of the highlighted cells.
Step 2:
- Secondly, go to the Visual Basic command from the Developer tab of the ribbon.
Step 3:
- Thirdly, go to the Module command from the Insert tab.
Step 4:
- Fourthly, copy the following VBA code.
- Consequently, you don’t have to play or save it.
'Set the function name
Function GetColorIndex(VarRange As Range) As Integer
'Set code to get color index number
GetColorIndex = VarRange.Interior.ColorIndex
End Function
Step 5:
- Then, in the worksheet type the following formula in cell C13 to get the color index number for its corresponding cell’s color.
=GetColorIndex(B13)
Step 6:
- Furthermore, press Enter and use the AutoFill feature to drag the formula to the lower cells.
- Consequently, you will be able to find out the color index numbers for all the colors.
Step 7:
- For this step, go to the VBA window again and paste the following code.
'Set the function name
Sub Hide_Rows_by_Interior_Color_Index()
'Address range object, the row till which the data remains
Dim Cell As Range, cRange As Range, EndRow As Long
'Set the rows property and count all the available rows in current worksheet
'Set the column number in which the highlighted cell is in current worksheet
EndRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
'Go through from row 4 in column B and check the cell is yellow
Set cRange = Range("B4:B" & EndRow)
For Each Cell In cRange
'Set the cells property and looking for interior color throguh color index number
If Cell.Interior.ColorIndex = 6 Then
'Set what happens after the condition is met
Cell.EntireRow.Hidden = True
'End the If function
End If
'Repeat the steps to complete until last row appears
Next Cell
End Sub
VBA Code Breakdown
- The function name is Hide_Rows_by_Interiror_Color_Index.
- The variable names are Cell , cRange which are range type variables, and the last one is EndRow which is long type variable.
- EndRow = ActiveSheet.Cells(Rows.Count, “B”).End(xlUp).Row: We set the row property and count all available rows and also set the column number in which the highlighted cell is.
- Set cRange = Range(“B4:B” & EndRow): We will use this to go through each row in column B from cell B4 till the last row.
- If Cell.Interior.ColorIndex = 6 Then Cell.EntireRow.Hidden = True: With the IF function, we will look for the color according to their color index number from the data set which is 6 for yellow. If the condition is met then this code will hide the entire row.
- End If: The end of the IF function.
- Next Cell: We will continue to look through the last row to meet the conditions.
Step 8:
- Then, save the code of the VBA window and select the play button or press F5.
Step 9:
- Finally, you will find that cell B6 contains the yellow color hidden from the worksheet along with the row.
Read More: How to Hide Part of Text in Excel Cells
Download Practice Workbook
You can download the free Excel workbook here and practice on your own.
Conclusion
That’s the end of this article. I hope you find this article helpful. After reading this article, you will be able to hide highlighted cells in Excel by using either of the two methods above. Please share any further queries or recommendations with us in the comments section below.
