Highlighting the Rows of Selected Cells

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

    r = Selection.Row
    c = Selection.Column
    rr = r
    cc = c

    With Columns(c).Interior
        .ColorIndex = 20
        .Pattern = xlSolid
    End With
    With Rows(r).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 r and rr 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 (2457) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Highlighting the Rows of Selected Cells.