CODEWITHSUNDEEP
vbaexcel
Concerned_Citizen
Concerned_Citizen

Reputation: 6835

Highlight Active Cell in Excel Without Resetting Existing Filled Cells

How can I automatically highlight the entire row where an active cell is highlighted without getting rid of other cells that are highlighted? (I want to highlight the entire row when there is an active cell and then un-highlight it when I move away.) t

I know the following VBA code does that but it eliminates all other cells filled with color. 

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
    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

Upvotes: 0

Views: 2757

Answers (1)

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19067

You could do that with Conditional Formatting and simple VBA Event. Follow these steps:

In Excel:
1. select range when you want to have highlighted rows... A1:J20 in this example
2. goto menu >> home >> conditional formatting >> new rule... >> use a formula to determine which cells to format
3. in formula textbox write this formula: =CELL("row") = ROW(A1)
4. set formatting stale by pressing 'format...' button
5. press ok

In VBA in module of the sheet for which you made the above action use this event:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Target.Calculate
End Sub

Upvotes: 2

Related Questions