How To Select Highlighted Rows In Excel

Please Note: This article is written for users of the post-obit Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Role 365. If you are using an earlier version (Excel 2003 or earlier), this tip may non piece of work for you lot. For a version of this tip written specifically for before versions of Excel, click hither: Highlighting the Rows of Selected Cells.

Highlighting the Rows of Selected Cells

Sometimes it is easy to lose runway 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 exercise this in Excel is to press Shift+Space Bar. The entire row is highlighted, and the selected prison cell remains the same. If y'all want to move to another prison cell in the same row (without changing the highlight), yous can use Tab to motility to the right and Shift+Tab to move to the left.

If you lot prefer to take Excel automatically highlight the row, you must rely upon a macro. The following i will do the trick:

Sub Worksheet_SelectionChange(ByVal Target Equally Excel.Range)     Static rr     Static cc      If cc <> "" And then         With Columns(cc).Interior             .ColorIndex = xlNone         Finish With         With Rows(rr).Interior             .ColorIndex = xlNone         Finish With     Stop If      rr = Selection.Row     cc = Selection.Column      With Columns(cc).Interior         .ColorIndex = 20         .Design = xlSolid     Cease With     With Rows(rr).Interior         .ColorIndex = xx         .Pattern = xlSolid     End With End Sub

Make sure you adhere the macro to the worksheet y'all are using at the time. All the code does is highlight the row and column the agile cell is at. When moving to some other prison 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 cavalcade. For merely highlighting the row, remove the chunks of code with cc in them. The only existent problem with this method is that if your canvas has any previous color-filled cells, these will be inverse to NoFill, erasing whatever color that was there.

If you would like to know how to employ the macros described on this page (or on whatsoever other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

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 lot can observe a version of this tip for the older menu interface of Excel here: Highlighting the Rows of Selected Cells.

Writer Bio

With more than l not-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a estimator and publishing services visitor. Acquire more than well-nigh Allen...

More than FROM ALLEN

Determining an ANSI Value

Yous may need to determine the numeric value of a grapheme in a macro. Yous can do that using the Asc function, described ...

Discover More

Symmetric Resizing of Graphics

Graphics can exist easily resized once they are placed in a worksheet. Here'south how you can make certain that the relationship ...

Discover More

Changing Chart Types

Want to modify an existing bar nautical chart to a different blazon of chart, such as a line nautical chart or a column chart? Information technology's easy to ...

Discover More