How to Highlight Selected Cells in Excel (5 Easy Ways)

Highlighting cells offers users to make entries stand out from the rest of the dataset and make sure entries get investigated. In this article, we use multiple Excel features, Formulas, and VBA Macro Code to highlight selected cells in Excel.

In a scenario, we have a dataset of different types of Products of different Categories with their sold quantity, Unit Price, and Total Price like the image below.

Product Sales-How to Highlight Selected Cells in Excel

Dataset for Download

5 Easy Ways to Highlight Selected Cells in Excel

Method 1: Using Define Name

To easily highlight the selected cell or range of cells, we can just assign a specific name to the selected cell or range of cells. We can assign a name using the Define Name feature available in Formulas Tab.

Step 1: Go to Formulas Tab > Select Define Name (in Define Names section).

Define Name-How to Highlight Selected Cells in Excel

Step 2: A New Name window will open.

In that window, type a name (i.e., Category) you desire to in the Name command box.

Select Define Name as Scope.

Click on the Icon right to the Refers to command box to select cells or a range of cells you like to assign the Name Category.

New Name

Step 3: Select the cells or range of cells (i.e., B4:B21) to define as Category.

Define Name Range-How to Highlight Selected Cells in Excel

Step 4: Tab ENTER to return to the New Name window then Click OK.

Assigned Name

Now if you Go to the Name Box (Left box to the Formula Bar box), you’ll see a name has been assigned.

Name Box

Name Box

You can assign names simply using Excel’s Name Box (Left to the Formula Bar Box). Excel’s Name Box will do the trick as Define Name in Formula Tab does.

Step 1: Select cells or a range i.e., C4:C21) of cells then Go to the Name Box and Type any Name (i.e., Product) you want to. Hit Enter.

Defined Name-Product

You assign Names by any means you want to as we described above. After assigning Names you can simply select any Names from the Name Box and the specific cells get highlighted.

Define Name result-How to Highlight Selected Cells in Excel

You can customize the selection by assigning multiple names depending on your data types.

Read More: How to Highlight a Cell in Excel (5 Methods)


Method 2: Using Format Cells Feature

Format Cells is a feature in Excel that offers various cell formattings such as Number Types, Alignment, Font, Border, Fill, and Protection. In this case, we use Fill to highlight selected cells or a range of cells.

Step 1: Select cells or a range of cells then Right-Click on any of the selected cells. The Context Menu pops up. From the Context Menu options, select Format Cells.

Format cells-How to Highlight Selected Cells in Excel

Step 2: Format Cells command window appears. In the Format Cells window, Select Fill as highlighting medium then Choose any color (i.e., Yellow) you like.

Click OK.

Format Cells

You can bring up the Format Cells window by Pressing CTRL+1 altogether.

Execution of the steps highlights the selected cells or range of cells as you expected them to be.

Format cells final result-How to Highlight Selected Cells in Excel

Read More: How to Highlight Cell Using the If Statement in Excel (7 Ways)


Method 3: Using Cell Styles

Excel’s Cell Styles offers a custom Cell Formatting for selected cells or a range of cells. In the case, you want to highlight the specific cells always in a fixed style, Cell Styles comes in handy.

Step 1: Go to Home Tab > Select Cell Styles (in Styles section) > Select New Cell Style.

Cell styles-How to Highlight Selected Cells in Excel

Step 2: The Style Command Box pops up. In the Style command box, Type a Style name (i.e., Highlight). Click on Format.

Style

Step 3: Click on the Fill section then Choose any color media (i.e., Yellow) to Highlight the cells. Click OK.

Fill color

Step 4: Again, Click OK on the Style dialog box.

You can select other cell formats as we mentioned in Method 2. However, as we want to simply Highlight the selected cells, we use Fill to be the Highlight medium and other cell formats option as optional.

styles window-How to Highlight Selected Cells in Excel

Step 5: Now, Select cells or a range of cells. After that Go to Home Tab > Select Cell Styles (in Styles section).

You’ll be seeing a custom Highlight cell style containing all the highlight options you put into Step 3.

Click on the Highlight cell style option.

Custom cell style

All the selected cells get highlighted by the Fill color (i.e., Yellow) you chose.

Cell styles final result-How to Highlight Selected Cells in Excel

Read More: How to Highlight Cells in Excel Based on Value (9 Methods)


Similar Readings


Method 4: Using Conditional Formatting (Row and Column)

To highlight active cell’s row and column, we can use conditional formatting. In this case, we use formulas to highlight active cell’s row and column together or individually.

Case 1: Both Row and Column in Same Color

To highlight both active cell’s row and column we use the OR function.

Step 1: Click on any cell (i.e., C12) you want both row and column to be highlighted.

Go to Home Tab > Select Conditional Formatting (in Style section)> Choose New Rule.

conditional formatting-How to Highlight Selected Cells in Excel

Step 2: New Formatting Rule window opens up. In the New Formatting Rule window, Choose Use a formula to determine which cell to format from Select a Rule Type option.

Paste the following formula under Edit the Rule Description box.

=OR(CELL("col")=COLUMN(),CELL("row")=ROW())

Here in the formula,

CELL(“col”)=COLUMN(); compares the selected cell’s column that is CELL(“col”) to the cell’s column to be highlighted that is COLUMN(). 

CELL(“row”)=ROW();compares the selected cell’s row that is CELL(“col”) to the cell’s row to be highlighted that is ROW().

We patch them with the OR function to highlight the cell’s row and column if either of the arguments is TRUE.

 Follow Step 3 of Method 3 to choose the Fill color (i.e., Yellow).

Click OK.

New formatting rule

You’ll see an outcome similar to the picture below.

New formatting rule result

Step 3: Again, Go to Conditional Formatting > Choose Manage Rules.

Manage Rule

Step 4: Select the whole range (i.e., B3:H21) in the Applies to command box. Click on Apply.

Apply

It results in highlighting the active cell’s row and column depicted in the following image.

conditional formatting final result-How to Highlight Selected Cells in Excel

Case 2: Row and Column in Different Color

We Select Clear Rules from the Conditional Formatting we did in the previous case before demonstrating this case. Clear Rules formats all the previous formulas we apply to highlight the cells.

Step 1: Repeat Steps 1 to 4 of Case 1.

Replace the formula with two New Rules.

=CELL("row")=ROW()
=CELL("col")=COLUMN()

These two formulas declare the same argument as they did in Case 1.

Select two different Fill colors following Step 3 of Method 3.

Select the whole dataset to Applies to the dialog box.

Click on Apply.

Rules Manager

The resultant picture will be similar to the picture below.

conditional formatting final result-How to Highlight Selected Cells in Excel

Case 3: Only Row or Column

You can also select any of the active cell’s row or column to highlight. In order to do so, you have to select only one Fill color for the row or column selecting the other one’s Fill color No Color.

Step 1:  Repeat Step 1 of Case 2.

Choose any of the Fill Color as No Color. In this case, we select the row formula to highlight the row with no color. It can be done vice versa. And the results also will be the opposite of the present instance.  

Click on Apply.

Rules Manager-How to Highlight Selected Cells in Excel

The outcome will be similar to the following picture.

conditional formatting result-How to Highlight Selected Cells in Excel

You can choose No Color as Fill Color for Column and the result will be the opposite.

Read More: How to Highlight Every 5 Rows in Excel (4 Methods)


Method 5: Using VBA Macro Code

VBA Macro Code enables dynamic highlighting of selected cells with a couple of lines of code.

Step 1: Press ALT+F11 altogether. Microsoft Visual Basic window opens up.

vba macro code-How to Highlight Selected Cells in Excel

Step 2: Double Click on Sheet3(VBAMacroCode) to bring up the Visual Basic Worksheet. Paste and Save the following code in the Worksheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Selection.Worksheet.Cells.FormatConditions.Delete
Selection.FormatConditions.Add xlExpression, , "TRUE"
Selection.FormatConditions(1).Interior.Color = vbYellow
End Sub

 

VBA Macro Code

The code deletes any previous cell formats and formats any selection particularly in Sheet3. In the code, we choose Yellow as the highlighting color, you can choose yours.

Step 3: Back to the Excel Worksheet, select any cells or range of cells; you’ll see it simultaneously get highlighted with Yellow color.

vba macro code result-How to Highlight Selected Cells in Excel

Read More: VBA to Change Cell Color Based on Value in Excel (3 Easy Examples)


 Conclusion

In this article, we use Excel’s features such as Define Name, Format Cells, Cell Styles, and Conditional formatting as well as VBA Macro Code to highlight selected cells. Defined Name, Format Cells, and Cell Styles features highlight any selected cells with no dynamic operability. Conditional formatting highlights active cell’s row and column offering one-dimensional usage. However, VBA Macro Code offers dynamic highlighting of selected cells as you move on a cell to cell or range to range. Hope above discussed methods fulfill your quest. Comment, if you have further queries or have something to add.


Related Articles

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo