How to Highlight a Row in Excel (2 Effective Ways)

For better readability, we need to highlight a row in Excel. It is very easy to highlight different rows manually in a small dataset. However, when you have to work with a huge number of datasets, you need to learn new techniques to highlight a row in Excel. In this tutorial, we will see several approaches to highlighting a row in Excel using conditional formatting and VBA.

Download Practice Workbook

Download the following Excel file for your practice.

2 Effective Methods to Highlight a Row in Excel

Here, we have a data set containing 5 columns and 9 rows including headings. Our mission is to highlight rows in different formats using different techniques.

1. Use Conditional Formatting to Highlight Row Based on Criteria

Conditional Formatting applies formatting over the whole cells of a row or multiple rows by checking the value in one cell of that row. It has some default rules using which you can highlight your data. Or, you can use a new rule based on your need.

You can use New Rules in Conditional Formatting with the following steps. We will show the steps as a general rule, then we will provide you with different formulas, which you can use to highlight rows based on different criteria.

Steps to Apply Conditional Formatting Using Custom Formula:

• First of all, select the dataset that consists of the rows to highlight.
• Now, go to the Home tab. Then look for the Styles group.
• Click on the Conditional Formatting button and the following drop-down list will appear.

• Press the New Rule button and the following window will appear.
• Select “Use a formula to determine which cells to format” as Rule Type.
• Now, type a suitable formula in the box that is named “Format values where this formula is true:“.
• Click on the Format button.

You will see the following window, named “Format Cells“.

• Now, go to the Fill tab and select a suitable background color.
• Then press OK.

The following window will appear again this time.

• Finally, press OK.

In the following subsections, we will provide you with certain formulas to highlight rows based on different criteria.

Criteria 1: Based on Text Match

Criteria:

Our goal is to highlight all the rows where the state name is “NY“.

Formula to Apply:

Result:

Criteria 2: Based on Number Match

Criteria:

Our goal is to highlight all the rows having a salary greater than 2000.

Formula to Apply:

Result:

Criteria 3: Based on Multiple Conditions

Criteria:

Our goal is to highlight all rows containing either ‘MI’ or ‘NY’.

Formula to Apply:

Result:

Again, suppose we need to highlight a row containing the first name ‘Jerry’ and state ‘GA’.

In this case, the formula will be,

Criteria 4: If Row Contains Blank Cells

Criteria:

Our goal is to find if there is any blank cell in a row. If found, then highlight it.

Formula to Apply:

Here, “” denotes blank

Result:

Criteria 5: Based on Drop-Down Selection

If you want a dynamic way to highlight a row by selecting a name from the drop-down selection list, you need to follow the steps below.

Creating a Drop-Down List:

• For creating a drop-down list, select a cell (H5 in this example). Then go to the Data tab. From the Data Tools group, click on the Data Validation drop-down and the Data Validation option. A Data Validation box will pop up.

• After selecting List as the validation criteria, a source field will appear. In the Source box, enter the following formula

• Then press OK.

A drop-down list is created in Cell H5 now.

Applying Conditional Formatting:

Now, apply Conditional Formatting as described at the very beginning with the following formula.

Formula to Apply:

Now, if you select an option from the drop-down list you have created, the row that contains it will be highlighted automatically.

Result:

Criteria 6: Highlight Duplicate Rows

Formula to Apply:

Where,

→ $B$5:$B$13, $C$5:$C$13, $D$5:$D$13, $E$5:$E$13, $F$5:$F$13 are the ranges.
→ $B4, $C4, $D4, $F4 are the criteria.

Result:

Criteria 7: Highlight Alternate Rows

Formula to Apply:

For odd rows,

For even rows,

Result:

Highlighted alternate odd rows-

Highlighted alternate even rows-

Read More: How to Highlight Entire Row in Excel with Conditional Formatting

Similar Readings

• Excel VBA to Highlight Cell Based on Value (5 Examples)
• How to Highlight a Cell in Excel (5 Methods)
• How to Highlight from Top to Bottom in Excel (5 Methods)
• How to Highlight Every 5 Rows in Excel (4 Methods)

2. Highlight an Active Row Using Excel VBA

If you want to highlight the active row using VBA code, follow the steps below.

Steps:

• Do Right-click on the Sheet name (VBA in this example) where you need to highlight the active row. Click on the View Code.

• A VBA window will pop up. Type the following code in the Code window.

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Static mRow
If mRow <> "" Then
With Rows(mRow).Interior
.ColorIndex = xlNone
End With
End If
Active_Row = Selection.Row
mRow = Active_Row
With Rows(Active_Row).Interior
.ColorIndex = 7
.Pattern = xlSolid
End With
End Sub

Here, you can change the highlighting color by changing the ColorIndex number.

• Close the VBA window.

Now, if you select a cell in the worksheet, the whole corresponding row will be highlighted.

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

Highlight Row in Excel: Knowledge Hub

• Using  Conditional Formatting to Highlight Entire Row
  
• Excel VBA: Highlight Active Row

Conclusion

In this tutorial, I have discussed 5 easy methods of how to highlight a row in Excel. I hope you found this article helpful. You can visit our website ExcelDemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.

Related Articles

• How to Highlight a Column in Excel (3 Methods)
• How to Fill Cell with Color Based on Percentage in Excel (6 Methods)
• How to Change Cell Color Based on a Value in Excel (5 Ways)
• How to Highlight Cell Using the If Statement in Excel (7 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 Cells in Excel Based on Value (9 Methods)