Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Highlighting the Rows of Selected Cells.
by Allen Wyatt
(last updated August 13, 2020)
Sometimes it is easy to lose track of where the selected cell is located in a worksheet. There are several ways you can locate the cell, but sometimes it would be handy to just have a way to highlight the whole row of the selected cell.
The easiest way to do this in Excel is to press Shift+Space Bar. The entire row is highlighted, and the selected cell remains the same. If you want to move to another cell in the same row (without changing the highlight), you can use Tab to move to the right and Shift+Tab to move to the left.
If you prefer to have Excel automatically highlight the row, you must rely upon a macro. The following one will do the trick:
Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) Static rr Static cc If cc <> "" Then With Columns(cc).Interior .ColorIndex = xlNone End With With Rows(rr).Interior .ColorIndex = xlNone End With End If rr = Selection.Row cc = Selection.Column With Columns(cc).Interior .ColorIndex = 20 .Pattern = xlSolid End With With Rows(rr).Interior .ColorIndex = 20 .Pattern = xlSolid End With End Sub
Make sure you attach the macro to the worksheet you are using at the time. All the code does is highlight the row and column the active cell is at. When moving to another cell, the code remembers the previous cell (by using variables declared as Static) and removes the highlighting from the previous rows and columns. This code highlights both the current row and column. For just highlighting the row, remove the chunks of code with cc in them. The only real problem with this method is that if your sheet has any previous color-filled cells, these will be changed to NoFill, erasing any color that was there.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10367) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. You can find a version of this tip for the older menu interface of Excel here: Highlighting the Rows of Selected Cells.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
The radical symbol is used frequently in some branches of mathematics. If you want to insert a radical symbol in a cell, ...Discover More
Excel provides keyboard shortcuts for a variety of purposes. This tip examines two such shortcuts, designed to allow ...Discover More
When creating a workbook, you may need to make changes on one worksheet and have those edits appear on the same cells in ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.