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.
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.
- 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.
- 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.
- 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.
- 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.
- Finally, you will see that only selected colored cells are shown in the worksheet.
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.
- 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
- 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.
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.
- 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).
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:
- Then, insert the Filter just like we described in the second method.
- Next, insert this formula in cell C14.
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.
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.