If your Excel dataset has a lot of columns, it becomes quite difficult to find data from one end to another end of a row. But if you generate a system in which whenever you select a cell in your dataset, the whole row will be highlighted, then you can easily find data from that row. In this article, I’ll show you how to highlight the active row in Excel in 3 different ways.
Suppose, you have the following dataset. You want to highlight a row whenever you select a cell of that row.
3 Methods to Highlight Active Row in Excel
1. Highlight Active Row Using Conditional Formatting
1.1. Apply Conditional Formatting
To highlight active row using conditional formatting, first,
➤ Select your entire worksheet by clicking on the top left corner of the sheet.
After that,
➤Go to Home > Conditional Formatting and select New Rule.
It will open the New Formatting Rule window. In this window,
➤ Select Use a formula to determine which cells to format option from the Select a Rule Type box.
As a result, a new box named Format values where this formula is true will appear in the bottom part of the New Formatting Rule window.
➤ Type the following formula in the Format values where this formula is true box,
=CELL("row")=CELL("row",A1)The formula will highlight the active row with your selected formatting style.
At last,
➤ Click on Format to set the color for highlighting.
1.2. Set Formatting Style to Highlight Active Row
After clicking Format, a new window named Format Cells will appear.
➤ Select a color with which you want to highlight the active row from the Fill tab.
You can also set a different number formatting, font, and border styles for the active row from the other tab of the other tabs of the Format Cells window if you want to.
➤ Click on OK.
Now, you will see your selected formatting style in the Preview box of the New Formatting Rule window.
➤ Click on OK.
Now,
➤ Select any cell of your dataset.
The entire row of the active cell will be highlighted with your selected color.
1.3. Refresh Manually When You Change the Active Cell
After selecting the first cell, if you select a cell from any other row, you will see the first row is still highlighted. This is happening because Excel hasn’t refreshed itself. Excel automatically refreshes itself when a change is made in any cell or when a command is given. But it doesn’t refresh automatically when you just change your selection. So, you need to refresh Excel manually.
➤ Press F9.
As a result, Excel will refresh itself and the active row will be highlighted.
So, now you just need to select a cell and press F9 to highlight the active row.
Read More: Excel Alternating Row Color with Conditional Formatting [Video]
2. Highlight Row with Active Cell in Excel Using VBA
You can also write a code to highlight the active cell using Microsoft Visual Basic Application (VBA). First,
➤ Right click on the sheet name (VBA) where you want to highlight the active row.
It will open the VBA window. In this VBA window, you will see the Code window of that sheet.
➤ Type the following code,
Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Static xRow
If xRow <> "" Then
With Rows(xRow).Interior
.ColorIndex = xlNone
End With
End If
Active_Row = Selection.Row
xRow = Active_Row
With Rows(Active_Row).Interior
.ColorIndex = 7
.Pattern = xlSolid
End With
End SubHere the code will change the color of the row with the selected cell with a color which has color index 7. If you want to highlight the active row with other colors you need to insert other numbers, inserted of 7 in the code.
➤ Close or minimize the VBA window.
Now, in your worksheet, if you select a cell, the whole row will be highlighted.
➤ Select another cell from a different row.
You will see now this row will be highlighted.
Read More: Highlight Row If Cell Contains Any Text
Similar Readings
- Hide Rows and Columns in Excel: Shortcut & Other Techniques
- Hidden Rows in Excel: How to Unhide or Delete Them?
- VBA to Hide Rows in Excel (14 Methods)
- How to Resize All Rows in Excel (6 Different Approaches)
- Unhide All Rows Not Working in Excel (5 Issues & Solutions)
3. Automatically Highlight Active Row Using Conditional Formatting and VBA
3.1. Apply Conditional Formatting
In the first method, you need to press F9 to refresh Excel after selecting a new row. You can make the process of refreshing automated by using a simple VBA code. In this method, I’ll show you how you can highlight the active row automatically using conditional formatting and VBA.
To do that first you have to define a name.
➤ Go to the Formulas tab and select Define Name.
It will open the New Name window.
➤ Type a name (for example HighlightActiveRow) in the Name box and type =1 in the Refers to box.
➤ Press OK.
Now,
➤ Select your entire worksheet by clicking on the top left corner of the sheet.
After that,
➤Go to Home > Conditional Formatting and select New Rule.
It will open the New Formatting Rule window. In this window,
➤ Select Use a formula to determine which cells to format option from the Select a Rule Type box.
As a result a new box named Format values where this formula is true will appear in the bottom part of the New Formatting Rule window.
➤ Type the following formula in the Format values where this formula is true box,
=CELL(A1)=HighlightActiveRowThe formula will highlight the active row with your selected formatting style.
At last,
➤ Click on Format to set the color for highlighting.
After clicking Format, a new window named Format Cells will appear.
➤ Select a color with which you want to highlight the active row from the Fill tab.
You can also set a different number formatting, font and border styles for the active row from the other tab of the other tabs of the Format Cells window, if you want to.
➤ Click on OK.
Now, you will see your selected formatting style in the Preview box of the New Formatting Rule window.
➤ Click on OK.
3.2. Apply Code for Automatic Refreshing
At this step,
➤ Right click on the sheet name (CF & VBA) where you want to highlight the active row.
It will open the VBA window. In this VBA window, you will see the Code window of that sheet.
➤ Type the following code in the Code window,
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With ThisWorkbook.Names("HighlightActiveRow")
.Name = "HighlightActiveRow"
.RefersToR1C1 = "=" & ActiveCell.Row
End With
End SubThe code will automate the refreshing process. Here, the name (HighlightActiveRow) must be the same as the name you have given in the Define Name box.
➤ Close or minimize the VBA window.
Now, in your worksheet, if you select a cell, the whole row will be highlighted.
If you select another cell, the row of that cell will be highlighted automatically. This time you won’t need to press F9 to refresh Excel.
Read More: How to Highlight Every Other Row in Excel
Conclusion
I hope now you know how to highlight the active row in Excel. If you have any confusion about any of the three methods discussed in this article, please feel free to leave a comment.
Related Articles
- Data Clean-up Techniques in Excel: Randomizing the Rows
- How to Collapse Rows in Excel (6 Methods)
- Unhide Rows in Excel (8 Quick Ways)
- How to Freeze Rows in Excel (6 Easy Methods)
- How to Group Rows with Same Value in Excel (6 Useful Ways)