How to Highlight Active Row in Excel (3 Methods)

Get FREE Advanced Excel Exercises with Solutions!

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.

Table of Contents hide

1. Highlighting Active Row Using Conditional Formatting

2. Using VBA to Highlight Rows with Active Cell in Excel

3. Automatically Highlight Active Row Using Conditional Formatting and VBA

Conclusion

1. Highlighting Active Row Using Conditional Formatting

Applying Conditional Formatting

To highlight active rows 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.

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

Read More: How to Highlight Selected Cells in Excel

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

2. Using VBA to Highlight Rows with Active Cell in Excel

You can also write 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 Sub

Here 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: Excel VBA to Highlight Cell Based on Value

3. Automatically Highlight Active Row Using Conditional Formatting and VBA

Applying 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,

=ROW(A1)=HighlightActiveRow

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

Applying 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 Sub

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

Download Practice Workbook

Highlight Active Row.xlsm

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.

16 Comments

A. Brow May 18, 2022 at 12:33 AM

Thank you for the crystal clear instructions. I just wanted to point out an error in section 3.1, under “Type the following formula in the Format…”. Your Conditional Formatting formula shows “=CELL(A1)=HighlightActiveRow” which doesn’t work when combined with the VBA. It should be “=Row(A1)=HighlightActiveRow”, like you show in the image after.
- Reply
  Durjoy Paul Aug 21, 2022 at 9:23 AM
  
  Hello Brow, yes you are right. It should be Row(A1) = HighlightActiveRow
Henrik Jun 10, 2022 at 6:01 PM

Hi, Thanks a lot for this article! Can I also make method B work for all sheets that I’m using? It already works well for the current sheet. I already have a personal.xlsb and an add-in (for UDFs), but inserting the code into a new module here, won’t work.
- Reply
  Durjoy Paul Aug 21, 2022 at 9:47 AM
  
  Hi Henrik, to highlight rows in Excel, you need to apply VBA code in that certain worksheet like this article. Otherwise, you need to run the code every time when you go to the next row and highlight it. This is a major disadvantage of this method. If you need to apply in all the worksheets, then you need to utilize the following code.
  
  Sub Highlights_Active_Row()
  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 Sub
  
  Note: Remember every time you go to the next row, you have to run the code every single time.
Amber Jul 11, 2022 at 11:01 PM

The only thing I’m struggling with is it won’t let me undo (ctrl + z) anything with the last method. Any suggestions?
- Reply
  Durjoy Paul Aug 21, 2022 at 9:40 AM
  
  Hi Amber, You can’t undo anything after applying the VBA code. This is one of the drawbacks of Excel.
Julie Sep 22, 2022 at 6:38 PM

Hi! I used option 2 with VBA only because the other options didn’t work with my document. The only problem is when I reopen the excel doc after closing it, the row of the last active cell stays highlighted no matter which cell I click. So, I end up with two highlighted, one row that’s constantly highlighted and the other one changes with the chosen active cell. How do I fix this?
- Reply
  Mithun Sep 25, 2022 at 4:45 PM
  
  Hello JULIE, thanks for your feedback. Use the below code to fix that-
  
  Sub Worksheet_SelectionChange(ByVal Target As Range)
  Static xRow
  Cells.Interior.ColorIndex = 0
  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 Sub
  
  *Or you can use an alternative way with the previous code, after opening the file, click on any cell on the previously highlighted row, and then only the active row will be highlighted.
Dale Hall Oct 26, 2022 at 5:43 PM

hi, I have used the VBA option as well (option 2), my only issue is if you click the titles of my table which is a different colour it then removes all the formatting of that, is there any way to add a range to the VBA to make it so this only works for certains cells or return it back to its original colour when not the active row?
- Reply
  Sabrina Ayon Oct 27, 2022 at 10:20 AM
  Hello, DALE HALL!
  Thanks for sharing your problem with us!
  You can set a range of cells to highlight using the following VBA code.
```
Sub Highlight()
For Each cell In Range("A5:A8")
     cell.EntireRow.Interior.ColorIndex = 6
Next
End Sub
```
  Hope this will help you!
  Good Luck!
  
  Regards,
  Sabrina Ayon
  Author, ExcelDemy.
behzad Nov 15, 2022 at 4:50 PM

hi
my excel dose not accept the formula’
it shows this message:
“we found a problem with this formula…
first i copied the formula, then i typed it manually,
still dosen’t work.
- Reply
  Eshrak Kader Nov 16, 2022 at 10:47 AM
  
  Hello Behzad,
  Thank you for your question. We’re sorry to hear that you’re facing difficulties with the formula. In fact, the ExcelDemy team has tested the Excel file following your comment and the formula appears to be working correctly.
  
  That said, it would be helpful if you could send us a screenshot of the issue that you’re experiencing.
FAHIM Feb 4, 2023 at 1:41 PM

HELLO CAN YOU DO ME A FAVOR.
IS THERE ANY WAY TO HIGHLIGHT ROWS AND COLUMNS OF MULTIPLE ACTIVE CELLS ONLY USING CONDITIONAL FORMATTING. SUCH AS WHEN SELECTING FROM C4 TO F10, ALL THE ROWS FROM 4 TO 10 AND ALL THE COLUMNS FROM C TO F GETS HIGHLIGHTED. IT WILL BE A GREAT HELP. THANK YOU.
Yousuf Khan Feb 5, 2023 at 3:54 PM

Hello FAHIM,

Yes, it is possible to highlight rows and columns of multiple active cells using conditional formatting in Microsoft Excel. To do so, you can use the following steps:

1. Initially, select the cells you want to apply the conditional formatting to.
2. Afterward, go to the Home tab and click the Conditional Formatting button in the Styles group.
3. Later, choose New Rule from the drop-down menu.
4. Next, select “Use a formula to determine which cells to format.”
5. Meanwhile in the formula box, enter the following formula:
=ROW()>=MIN(ROW($C$4:$F$10))
6. Further, click the Format button and select the fill color you want to use.
7. Lastly, click OK to close the Format Cells dialog box.
This approach uses a relative reference for the row and column in the formula, allowing the conditional formatting to adjust automatically as cells are selected.
Kobus Feb 16, 2023 at 3:52 PM

Hi I inserted the code for the following:

2. Highlight Row with Active Cell in Excel Using VBA

When I click on a cell it gives the following error:

“Run-time error ‘1004’:

Application-defined or Object-defined error”

If I press “Debug” it displays a yellow arrow at:
.ColorIndex = 7
- Reply
  Mehedi Hasan Feb 19, 2023 at 4:16 PM
  
  Hello KOBUS,
  Thank you for your question. We’re sorry to hear that you’re facing difficulties with the VBA code. In fact, the ExcelDemy team has tested the Excel file and the code with other workbooks following your comment and the code appears to be working correctly.
  However, you can check the following 2 steps.
  1. Follow the individual steps properly.
  2. You have to insert the code by using the View Code option of a particular sheet. It will not work if you insert it in a VBA Module.
  I hope this will solve your issue. If you still face problems, please feel free to comment again or send your workbook so that I can check the issue.

How to Highlight Active Row in Excel (3 Methods)

1. Highlighting Active Row Using Conditional Formatting

2. Using VBA to Highlight Rows with Active Cell in Excel

3. Automatically Highlight Active Row Using Conditional Formatting and VBA

Conclusion

Related Articles

Leave a reply Cancel reply