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.
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.
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:
=$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.
- Next, go to Home >> Conditional Formatting and select New 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.
- Finally, click on any cell in the dataset and the whole active row will be highlighted.
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.
- 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.
3. Highlight Active Row While Scrolling
- First, right-click on the sheet name to select 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
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.
- Next, press Shift + Spacebar together, and the whole active row will be selected.
- After that, from the Fill color option in the Font ribbon.
Highlight Active Row and Column at Once
- First, we will apply VBA code to highlight active rows and columns in the following dataset.
- Then, right-click on the sheet name and select 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
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.
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
- Highlight Every Other Row
- Highlight Entire Row in Excel with Conditional Formatting
- Highlight Active Row
- Highlight Row If Cell Is Not Blank
- Highlight Row If Cell Contains Any Text
- Highlight Every 5 Rows
- How Can You Alternate Row Colors in Excel?
<< Go Back to Highlight in Excel | Learn Excel