How to highlight active row in excel and then return to base background color in VBA

How do you highlight an active row in excel in VBA. and then when another row is selected, return that row to base background color, and highlight the new row.

Also how to clear all rows highlighted, using a clear button on the user form.

so there are tow question here, one to high light and unhighlight active rows, and the other to just clear all high lights by pressing a clear button on the form.

I know I can highlight a row using Ret.EntireRow.Interior.ColorIndex = 6 but i cant find code to unhighlight.

Thanks for your help.

Solution

You can use your 'clear all' functionality before changing the color of the row of the cell that you navigated to.

Open the VB Editor and right click --> view code on the worksheet that you want the row highlighting to take place.

Paste in this code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Me.Range("A1:XFD1048576").Interior.ColorIndex = 0
    Target.EntireRow.Interior.ColorIndex = 6

End Sub

This code operates as follows: whenever a user changes his or her selected cell(s) on the sheet, the code will first clear the existing highlighting away in the entire sheet and then will apply new highlighting to the row of the target cell the user has moved to.

This line of code:

Worksheets("YourSheetName").Range("A1:XFD1048576").Interior.ColorIndex = 0

Will clear the colors from all cells in the worksheet.

You may want to limit the Range("A1:XFD1048576") to the usable range on your workbook as this will increase performance. On my machine I see a very subtle, but still noticeable, delay in the colors when I move the cells (because I am clearing all cells in the sheet instead of just the ones I want). If you do this, you probably wouldn't want to use the .EntireRow attribute, instead you would have to enumerate how far along the workbook you want the row to be highlighted.

Update

Try this code below, which eliminates the need to clear the entire worksheet. I used .ColorIndex=xlNone instead of setting it to 0 which should preserve your table formatting. I tested in Excel 2010 and I formatted some data as a table, it highlights the correct row and unhighlights the other row as well as leaving the table formatting in tact.

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Static rr

    If rr <> "" Then
        With Rows(rr).Interior
            .ColorIndex = xlNone
        End With
    End If


    r = Selection.Row
    rr = r

    With Rows(r).Interior
        .ColorIndex = 6
        .Pattern = xlSolid
    End With
End Sub

The trick is using Static. This allows the variable to continue to exist after termination of the procedure, so it remembers the last row it highlighted and then performs the un-highlight action accordingly.

The procedure first checks to see that rr is set, if it is not then it moves on, if it is then rr represents the row that was previously highlighted.