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

Get FREE Advanced Excel Exercises with Solutions!

This article describes different ways 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. You will also learn several shortcut methods related to highlighting rows in Excel.

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.  Read the article to highlight rows in Excel by yourself.

Highlight a Row in Excel


How to Highlight a Row in Excel: 4 Effective Methods

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.

Sample Excel Dataset


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.

Use Excel Conditional Formatting to Highlight Row Based on Criteria

  • 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.

Use Excel Conditional Formatting to Highlight Row Based on Criteria

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

Use Excel Conditional Formatting to Highlight Row Based on Criteria

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

The following window will appear again this time.

Use Excel Conditional Formatting to Highlight Row Based on Criteria

  • 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:

=$E5="NY"

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:

=$F5>2000

Result:


Criteria 3: Based on Multiple Conditions

Criteria:

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

Formula to Apply:

=OR($E5="MI",$E5="NY")

Result:

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

In this case, the formula will be,

=AND($C5="Jerry",$E5="GA")

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:

=COUNTIF($B5:$F5,"")>0

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
=$B$5:$B$12
  • 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:

=$B5=$A$3

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:

=COUNTIF($B$5:$B$13,$B4,$C$5:$C$13,$C4,$D$5:$D$13,$D4,$E$5:$E$13,$E4,$F$5:$F$13,$F4)>1

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,

=ISODD(ROW())

For even rows,

=ISEVEN(ROW())

Result:

Highlighted alternate odd rows-

Highlighted alternate even rows-


Criteria 8: Highlight Active Row

  • First, select the entire worksheet using Ctrl+A or click on the top left corner of the sheet.

Highlight Active Row Using Conditional Formatting

  • Next, go to Home >> Conditional Formatting and select New Rule.

Creating new conditional formatting rule

  • After that, it will open the New Formatting Rule window.
  • Then, select Use a formula to determine which cells to format option from the Select a Rule Type
  • Now, insert the following formula in the Format values where this formula is true

=CELL("row")=CELL("row",A1)

Here, the CELL function returns the row number of a specific cell or active cell.

  • Next, click on the Format button and choose the color of your preference.
  • At last, click on OK.

Setting up new formatting rule

  • Finally, click on any cell in the dataset and the whole active row will be highlighted.

Highlighted row


2. Highlight an Active Row Using Excel VBA

If you want to highlight the active row using the 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.

Highlight an Active Row Using Excel VBA

  • 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

Highlight an Active Row Using Excel VBA

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.


3. Highlight Active Row While Scrolling

  • First, right-click on the sheet name to select View code.

Opening view code

  • After that, insert the following VBA code.
Private Sub Worksheet_SelectionChange(ByVal SelectedRange As Range)

    Cells.Interior.Color = RGB(255, 255, 255) ' Sets the fill color to white

    SelectedRange.EntireRow.Interior.Color = RGB(0, 255, 255) ' Sets the fill color to cyan

End Sub

VBA code highlight row while scrolling

This line Private Sub Worksheet_SelectionChange(ByVal SelectedRange As Range) declares the event  Worksheet_SelectionChange, it activates when the selection changes in the worksheet.

  • Now, in the sheet, scroll using the down arrow on the keyboard. The code will highlight the active row while scrolling.

4. Shortcuts to Highlight Row in Excel

Task Shortcut
Highlight Active Row Select a cell in the row >> Shift + Spacebar
Highlight an Entire Row Click on the row number
Highlight Non-Adjacent Cells Select the first cell >> Hold Ctrl key >> Select the last cell
Highlight the Entire Worksheet Ctrl + A
Highlight Rows Above or Below the Active Row Shift + Up Arrow/Down Arrow
Highlight Rows to the Left or Right of the Active Column Shift + Left Arrow/Right Arrow
Highlight Rows with Specific Text Ctrl+F (Find & Replace) >> Input text >> Find All

Basically, most of the above shortcuts are for selecting cells in Excel. After using the shortcuts, you have to pick a font color to highlight the cells in Excel. Let’s apply the first shortcut.

  • First, select any cell in the dataset.

Active cell

  • Next, press Shift + Spacebar together, and the whole active row will be selected.

Selecting active row using shortcut

  • After that, from the Fill color option in the Font ribbon.

Highlighting active row


Highlight Active Row and Column at Once

  • First, we will apply VBA code to highlight active rows and columns in the following dataset.

Highlighting active row and column

  • Then, right-click on the sheet name and select View Code.

Selecting view code

  • Now, insert the below-mentioned VBA code in the module.
Private Sub Worksheet_SelectionChange(ByVal SelectedRange As Range)

  If SelectedRange.Cells.Count > 1 Then Exit Sub

  Application.ScreenUpdating = False

  ' Clear the color of all cells

  Cells.Interior.Color = RGB(255, 255, 255) ' White color

  With SelectedRange

    ' Highlight row and column of the selected cell

    .EntireRow.Interior.Color = RGB(200, 200, 200) ' Light gray color

    .EntireColumn.Interior.Color = RGB(0, 255, 255) ' Cyan color

  End With

  Application.ScreenUpdating = True

End Sub

VBA code highlight row and column

Code Breakdown

  • If SelectedRange.Cells.Count > 1 Then Exit Sub: If the selected range is more than 1, then it will exit the sub. It will execute the remaining code.
  • Cells.Interior.Color = RGB(255, 255, 255) ‘ White color: This line makes the cell background color white.
  • .EntireRow.Interior.Color = RGB(200, 200, 200) ‘ Light gray color: This line makes the entire row background color light gray.
  • .EntireColumn.Interior.Color = RGB(0, 255, 255) ‘ Cyan color: This line makes the entire column background color Cyan.
  • Finally, you will see that it will highlight both the row and the column of the active cell.

Highlighting Row and Column


Things to Remember

  • Check if any of your rows contains merged cells. Unmerge the cells before highlighting rows. Otherwise, it may not work as expected.
  • If you have frozen panes in your Excel sheet, then the frozen row might not be highlighted if you select a row below the frozen pane. Adjust the frozen panes as needed or select a row within the unfrozen area.

Frequently Asked Questions

1. Can I remove the highlighting from a row in Excel?

Yes, you can remove the highlighting from a row in Excel. To do this, select the highlighted row, go to the “Home” tab, click on the “Fill Color” button, and choose “No Fill” or “Default” to remove the highlighting.

2. How can I quickly highlight alternating rows in Excel?

To quickly highlight alternating rows in Excel, you can use conditional formatting. In the “Conditional Formatting” button, choose “New Rule,” select the option “Use a formula to determine which cells to format,” and enter the formula “=MOD(ROW(),2)=0” for one formatting rule and “=MOD(ROW(),2)=1” for the other.

3. Does highlighting a row affect the data or formulas in Excel?

No, highlighting a row in Excel does not affect the underlying data or formulas.


Download Practice Workbook

Download the following Excel file for your practice.


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. Please, drop comments, suggestions, or queries if you have any in the comment section below.


How to Highlight a Row in Excel: Knowledge Hub


<< Go Back to Highlight in Excel | 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.
Hafizul Islam
Hafizul Islam

Hafizul Islam is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo