January 29, 2015

How To Highlight Entire Current Row And Column In Excel

How can I use VBA or conditional formatting to highlight the entire current row and column of the current cell?

In your sheets worksheets selection change event, you can use something like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Const HIGHLIGHT_COLOR As Long = 4

    'remove past colors
    ActiveSheet.Cells.Interior.ColorIndex = xlNone

    With Me
        .Columns(Target.Column).Interior.ColorIndex = HIGHLIGHT_COLOR
        .Rows(Target.Row).Interior.ColorIndex = HIGHLIGHT_COLOR
    End With
End Sub

Here’s one way:

ActiveSheet.Rows(ActiveCell.Row).Interior.Color = RGB(r, g, b)
ActiveSheet.Columns(ActiveCell.Column).Interior.Color = RGB(r, g, b)

You can fill in r,g & b to achieve the highlighting color you want. If you use Interior.Color, you can use the vbColor constants instead of RGB values if you like. vbRed, vbGreen, vbYellow, etc.

Speak Your Mind