Excel (Un)Highlight Rows on Click 1

[HProutt]

I have a spreadsheet where I can click on any cell and the entire row will get highlighted in the color of my choice. For example, if I click on G10, all of row 10 will highlight in a light blue color. Now I want to be able to click anywhere in row 10 again and have the row go back to the original formatting.

Currently, my code looks like:

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

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

r = Selection.Row

rr = r

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

It was taken from another blog and altered as best as I could so if there is a way to clean it up that makes sense I am open to it.

 

[SkipVought]

Hi,

There is no way to, "have the row go back to the original formatting." You would need to store the format of the row (and each cell can have a different format in the row) before you change the format.

But here's something that removes the format on the second click...

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Static rr
    Static cc   'what did this do? something with COLUMN????
    Dim r
    
    r = Target.Row

    If r = rr Then
        Rows(rr).Interior.ColorIndex = xlNone
    Else
        rr = r
        With Rows(r).Interior
            .ColorIndex = 37
            .Pattern = xlSolid
        End With
    End If
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With Target
        If .Interior.ColorIndex = xlNone Then
            .EntireRow.Interior.ColorIndex = 37
        Else
            .EntireRow.Interior.ColorIndex = xlNone
        End If
    End With
End Sub

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[HProutt]

yes Static cc used to have to do with columns. Both rows and columns were highlighted using the original code that I got, so I stripped all column-related code out. (save for that line) That code works great, thanks for the help.