Highlight Current Row Without Deleting Existing Cell Colours

Question

I found the code below, and while it highlights the entire row it also removes the color from any previously colored cell.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub

    Application.ScreenUpdating = False
    ' Clear the color of all the cells
    Target.Parent.Cells.Interior.ColorIndex = 0
    With Target
        ' Highlight the entire row and column that contain the active cell
        .EntireRow.Interior.ColorIndex = 8
    End With
    Application.ScreenUpdating = True

End Sub

I would like to highlight the entire row on selection of a cell (that may already be colored), but when I move to a cell in a different row, the previously highlighted row should return to its previous color.

Is there a way to modify the previously selected cells/rows?

Answer 1

Conditional formatting overrides "regular" formatting (without replacing it), so if you don't already have some CF applied it's a convenient way to highlight a row without zapping any existing cell colors.

Here's a very basic example:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub

    Application.ScreenUpdating = False

    Me.Cells.FormatConditions.Delete

    With Target.EntireRow.FormatConditions.Add(Type:=xlExpression, _
                                               Formula1:="=TRUE") 
        .SetFirstPriority
        .Interior.Color = 65535
    End With

    Application.ScreenUpdating = True

End Sub

Answer 2

You will need to store the format and row number somewhere then paste it back upon selecting a new row.

This will store the exiting format and row number before the highlight to the 1,040,000 row on the same sheet.

Then when another row is selected it will check if there is formatting there and replace the row from where it was copied back.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub

    Application.ScreenUpdating = False
    'test if formatting exist and copy it back to the row just left.
    If Cells(1040000, 1) <> "" Then
        Rows(1040000).Copy
        Rows(Cells(1040000, 1).Value).PasteSpecial Paste:=xlPasteFormats
    End If
    'Copy formating to store
    Rows(Target.Row).Copy
    Rows(1040000).PasteSpecial Paste:=xlPasteFormats
    Cells(1040000, 1) = Target.Row


    With Target
        ' Highlight the entire row and column that contain the active cell
        .EntireRow.Interior.ColorIndex = 8

    End With

    Application.CutCopyMode = False
    Application.ScreenUpdating = True

End Sub

Answer 3

This is what I can come up with:

Public rngPreviousColor As Range
Public lngColor         As Long

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub

    If Not rngPreviousColor Is Nothing Then
        rngPreviousColor.Interior.ColorIndex = lngColor
    End If

    Set rngPreviousColor = Target.EntireRow
    lngColor = rngPreviousColor.Interior.ColorIndex

    With Target
        .EntireRow.Interior.ColorIndex = 8
    End With

End Sub

The idea is that the other row is the whole in one color and we save the row as a range rngPreviousColor and the color as lngColor.

Answer 4

I have created an add-in for this. Download, enable content, and click the install button. The add-in creates three buttons on the View ribbon tab that toggle the highlighting.

• Uses Conditional Formatting, so no overriding cell color settings.
• All code is in the add-in, so no additional VBA required.