How to Select Highlighted Cells in Excel (4 Easy Techniques)

Get FREE Advanced Excel Exercises with Solutions!

An organized and neat presentation is always a priority especially when we work with a large dataset. This is why most Excel users prefer to highlight cells based on certain conditions. But sometimes we need to select those highlighted cells in Excel separately for ease of understanding. Therefore, in this article, we will learn how to select highlighted cells in Excel with 4 easy techniques.


How to Select Highlighted Cells in Excel (4 Easy Techniques)

To describe, here is a dataset with the information of Customer Name, Product ID, and Price of a company. Here, we highlighted the cells that have similar Product ID numbers and Price amounts.

How to Select Highlighted Cells in Excel


Now, we will try to select the highlighted cells separately with the techniques below.

1. Use Find Command for Selecting Highlighted Cells

The first method will guide you in selecting highlighted cells with the help of the Find command in Excel. It will search for the specific colored cells and select them afterward. Let’s check the steps below:

  • In the beginning, select cell range C5:D12 as these cells contain highlights.

Use Find Command for Selecting Highlighted Cells

  • Then, go to the Home tab and click on the Find & Select drop-down under the Editing group.

  • After this, select Find from the drop-down menu.

  • Next, a new window will appear named Find and Replace.
  • Here, click on Format.

  • Thereafter, select the color from the Background Color palette under the Fill section that you want to find among the highlighted cells.

Use Find Command for Selecting Highlighted Cells

  • Next, press OK.
  • Now, click on Find All.

  • Here, you will see the list of cells that are highlighted with the selected color.
  • Now, press Ctrl + A to select all of them.

How to Select Highlighted Cells in Excel

  • Then, press Close.
  • Finally, you will see that the specific colored cells are selected.

  • Along with that, you can also get the values of the Average, Count, and Sum of the selected cells.

Read More: Select All Cells with Data in Excel 


2. Indicate Highlighted Cells with Filter Tool in Excel

This second method will guide you in selecting the highlighted cells using the Filter tool in the dataset. This tool will separate data according to categories from the dataset. Follow the process below:

  • First, click on any highlighted cell of your dataset.
  • Then, go to the Home tab and select Sort & Filter under the Editing group.

Indicate Highlighted Cells with Filter Tool in Excel

  • Here, select Filter from the drop-down section.
  • Otherwise, press Ctrl + Shift + L on your keyboard to get this tool.

  • Now, you will see an arrow icon beside each title in the dataset.

  • After this, click on the arrow beside any of the columns that have highlighted cells.
  • For example, we selected the Product ID filter icon.
  • Following, go to Filter by color and choose the color you want to specify.

Indicate Highlighted Cells with Filter Tool in Excel

  • Finally, you will see that only selected colored cells are shown in the worksheet.

Additional Tip: You can also find the Filter option from the Data tab under the Sort & Filter group.

Read More: Select All Cells with Data in a Column in Excel 


3. Insert Excel VBA Code to Select Highlighted Cells

Excel VBA code is a programming language. It helps to perform thousands of tasks with a single code. In this section, we will discuss the process of selecting highlighted cells with VBA code. Let’s see how it works.

  • Firstly, go to select cell range B5:D12.
  • Secondly, go to the Developer tab and select Visual Basic under the Codes group.

Insert Excel VBA Code to Select Highlighted Cells

  • Thirdly, select Module under the Insert section in the Visual Basic window.

  • After this, insert this code on the blank page.
Sub select_highlighted_cells()
Dim rng As Range
Dim selection As Variant
Set rng = Application.selection
mystring = ""
    For Each cellitem In rng
         If cellitem.Interior.ColorIndex <> -4142 Then
            mystring = mystring & cellitem.Address & ","
         End If
    Next
    If mystring = "" Then
        MsgBox "No highlighted cell found"
    Else
        Range(Left(mystring, Len(mystring) - Len(","))).Select
    End If
End Sub

Insert Excel VBA Code to Select Highlighted Cells

  • Following, click on the Run Sub button or press F5 on your keyboard.

  • Lastly, select Run on the Macros window.

  • Finally, you can see that the highlighted cells are selected in the worksheet.

Read More: How to Select Cells with Certain Value in Excel


4. See Highlighted Cells by Creating Search Box in Excel

In this last technique, we will select highlighted cells with a different color. For this, we will be creating a Search box in our Excel file. Simply follow the steps below:

  • In the beginning, create a Search box below the dataset.
  • Besides this, insert the value you want to find.
  • For example, we entered the value 49 here.

See Highlighted Cells by Creating Search Box in Excel

  • After this, select cell range B5:D12.
  • Then, go to Home and select Conditional Formatting under the Styles group.

  • Here, select the option New Rule.

  • Now, you will be directed to the New Formatting Rule dialogue box.
  • In this window, select the rule like the image below:

  • Next, insert this formula in the marked box (see screenshot).
=SEARCH($C$14,$B5&$C5&$D5)

See Highlighted Cells by Creating Search Box in Excel

Here, we used the SEARCH function to return the position of a numeric value from one text string and put it inside another cell.

  • Following, click on the Format button.

  • After this, select your preferred color from the Background Color palette under the Fill section.

  • Then, hit OK.
  • Lastly, press OK to close the dialogue box.

  • Finally, you will see the searched value is showing with the selected color.

Read More: How to Select Random Cells in Excel


How to Count Highlighted Cells in Excel

So far we learned how to select highlighted cells in Excel. But apart from this, we may need to get the number of cells of a specific color to get more precise information. To do this, follow the steps below:

  • In the beginning, create a Count box like the image below:

How to Count Highlighted Cells in Excel

  • Then, insert the Filter just like we described in the second method.
  • Next, insert this formula in cell C14.
=SUBTOTAL(102,D5:D12)

How to Count Highlighted Cells in Excel

Here, we used the SUBTOTAL function to calculate all the cells. Afterward, put argument number 102 as COUNT. Lastly, provided the cell reference as C5:D12.

  • Following, press Enter.
  • Initially, you will see the total number of cells on the box.

  • Now, click the filter icon on the Price column.
  • Then, apply the Filter by Color tool to select a color.

  • Finally, you will see the number of specific colored cells.

 


Download Practice Workbook

Download this sample file to try at home.


Conclusion

Concluding the article, with the hope that it was a helpful article for you on how to select highlighted cells in Excel with 4 easy techniques. Try any of them and let us know your suggestions in the comment box.


Related Articles


<< Go Back to Select Cells | Excel Cells | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sanjida Mehrun Guria
Sanjida Mehrun Guria

Hello! Welcome to my blog. I have completed my Bachelor in Architecture from Ashanullah University of Science & Technology, Dhaka. I am a passionate, goal-oriented person with an immense thirst for knowledge and an attitude to grow continuously. Besides Architectural work, I find it very enthusiastic to work in Excel blogging. Exceldemy is a platform where I have got the opportunity to flourish my skill in Microsoft Excel and therefore, here I will be posting blogs related to... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo