How to Hide Highlighted Cells in Excel (2 Ways)

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.


Download Practice Workbook

You can download the free Excel workbook here and practice on your own.


2 Easy Ways to Hide Highlighted Cells in Excel

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.

Easy Ways to Hide Highlighted Cells in Excel

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.

Easy Ways to Hide Highlighted Cells in Excel

Step 2:

  • Secondly, we will write down the colors of each cell from column B in our helper column.

Easy Ways to Hide Highlighted Cells in Excel

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.

Easy Ways to Hide Highlighted Cells in Excel

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.

Easy Ways to Hide Highlighted Cells in Excel

Step 5:

  • Finally, you will see that cell B6 in row 6 containing the yellow color is hidden from the worksheet.

Easy Ways to Hide Highlighted Cells in Excel

Step 6:

  • Furthermore, if you want to show the hidden cell, go to the filter drop-down again and mark the specific cell color.

Easy Ways to Hide Highlighted Cells in Excel

Step 7:

  • Finally, you will see the highlighted cell with its corresponding row again.

Easy Ways to Hide Highlighted Cells in Excel

Read More: How to Hide Cells in Excel Until Data Entered (2 Easy Ways)


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.

Easy Ways to Hide Highlighted Cells in Excel

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.

Easy Ways to Hide Highlighted Cells in Excel

Step 3:

  • Thirdly, a dialogue box named Format Cells will appear.
  • From that box go to the Fill tab.
  • Then, choose More Colors….

Easy Ways to Hide Highlighted Cells in Excel

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.

Easy Ways to Hide Highlighted Cells in Excel

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.

Easy Ways to Hide Highlighted Cells in Excel

Step 6:

  • After selecting, you will see the VBA window.
  • Then, choose the Module command from the Insert tab.

Easy Ways to Hide Highlighted Cells in Excel

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

Easy Ways to Hide Highlighted Cells in Excel

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.

Easy Ways to Hide Highlighted Cells in Excel

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.

Easy Ways to Hide Highlighted Cells in Excel

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.

Easy Ways to Hide Highlighted Cells in Excel

Step 2:

  • Secondly, go to the Visual Basic command from the Developer tab of the ribbon.

Sample Data Set

Step 3:

  • Thirdly, go to the Module command from the Insert tab.

Sample Data Set

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

Sample Data Set

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)

Sample Data Set

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.

Sample Data Set

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

Sample Data Set

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.

Sample Data Set

Step 9:

  • Finally, you will find that cell B6 contains the yellow color hidden from the worksheet along with the row.

Sample Data Set

Read More: How to Hide Part of Text in Excel Cells (2 Effective Methods)


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.


Related Articles

 

Araf

Araf

I am Araf. I have completed my B.Sc in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Currently I am working as an Excel & VBA Content Developer in Softeko. With proper guideline and aid of Softeko I want to be a flexible data analyst. With my acquired knowledge and hard work, I want to contribute to the overall growth of this organization.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo