Maruf Islam
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.
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).
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.
Step 3: Select the cells or range of cells (i.e., B4:B21) to define as Category.
Step 4: Tab ENTER to return to the New Name window then Click OK.
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
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.
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.
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.
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.
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.
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.
Step 2: The Style Command Box pops up. In the Style command box, Type a Style name (i.e., Highlight). Click on Format.
Step 3: Click on the Fill section then Choose any color media (i.e., Yellow) to Highlight the cells. Click OK.
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.
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.
All the selected cells get highlighted by the Fill color (i.e., Yellow) you chose.
Read More: How to Highlight Cells in Excel Based on Value (9 Methods)
Similar Readings
- How to Change Cell Color Based on a Value in Excel (5 Ways)
- Highlight Cells That Contain Text from a List in Excel (7 Easy Ways)
- How to Compare Two Excel Sheets and Highlight Differences (7 Ways)
- How to Highlight Selected Text in Excel (8 Ways)
- Excel Cell Color: Add, Edit, Use & Remove
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.
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.
You’ll see an outcome similar to the picture below.
Step 3: Again, Go to Conditional Formatting > Choose Manage Rules.
Step 4: Select the whole range (i.e., B3:H21) in the Applies to command box. Click on Apply.
It results in highlighting the active cell’s row and column depicted in the following image.
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.
The resultant picture will be similar to the picture below.
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.
The outcome will be similar to the following picture.
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.
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
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.
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.
