Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. 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 September 5, 2015)
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, and 2013. You can find a version of this tip for the older menu interface of Excel here: Highlighting the Rows of Selected Cells.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
Need to get rid of spaces in a range of cells? There are two ways you can approach the task, as described here.Discover More
Want a quick way to enter a series of single digits into consecutive cells? The best approach is with a macro, and this tip ...Discover More
Edit a group of workbooks at the same time and you probably will find yourself trying to copy information from one of those ...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.