Excel 2010 VBScript – Highlight Row Issue

Question

I was wondering if someone had any suggestions to this. I want the row to highlight below row 6 when a cell is clicked on. So if I click on A7, then row 7 will highlight. If I then click on B9, row 7 will have the highlight removed and row 9 will then highlight. I did find code that does work for what I need and have customized it a little. Everything works exactly the way I need it to work, except for when Excel is saved, closed out, and reopened.

If row 9 is highlighted, and the spreadsheet is saved, closed, and reopened, row 9 will remain highlighted (even when another cell is clicked on). So now I have 2 rows highlighted. In order to fix this once the spreadsheet is opened back up is to click on a different row and then click back on row 9. Then it will be back to 1 highlighted row.

Does anyone have a solution for this? Below is the code that I am using.

Thanks for any help someone can provide,

Chris

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


ActiveSheet.Unprotect

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 = 20
    .Pattern = xlSolid
End With

ActiveSheet.Protect

End Sub

Answer 1

The following combination of code seems to be working; I'm highlighting the entire row each time.

Private lastRow As Long

Private Sub Worksheet_Activate()
    lastRow = ActiveCell.Row
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If lastRow <> 0 Then
        Rows(lastRow).EntireRow.Interior.ColorIndex = xlNone
        If Target.Row > 6 Then
            Target.Rows(1).EntireRow.Interior.ColorIndex = 20
        End If
        lastRow = Target.Row
    Else
        lastRow = Target.Row
    End If
End Sub

Actually, it probably needs a bit of work. However, it might be a starting point for you.

Answer 2

I wrote my own code instead of trying to work with the code I found. This works a lot better. It also allows the user to specify their own range of rows to highlight.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Application.EnableEvents = False
ActiveSheet.Unprotect

Dim iFirstCol As Integer
Dim iLastCol As Integer
Dim iFirstRow As Integer
Dim iLastRow As Integer
Dim iColor As Integer

'''Only adjust the below numbers to fit your desired results.'''
iFirstCol = 1 'Change this number to the number of the first column that needs to be highlighted. Column A = 1.
iLastCol = 15 'Change this number to the number of the last column that needs to be highlighted. Column A = 1.
iFirstRow = 7 'Change this number to the number of the first row that needs to be highlighted.
iLastRow = 500 'Change this number to the number of the last row that needs to be highlighted.
iColor = 20 'Change this number to use a different highlight color.
'''End of changes, do not change anything else.'''

'The row highlight will only be applied if the selected range is within this if statement criteria.
If Target.Row > iFirstRow - 1 And Target.Row < iLastRow + 1 And Target.Column > iFirstCol - 1 And Target.Column < iLastCol + 1 Then

    'Resets the color within the full range when cell selection changed.
    ActiveSheet.Range(ActiveSheet.Cells(iFirstRow, iFirstCol), ActiveSheet.Cells(iLastRow, iLastCol)).Interior.Color = xlNone

    'Applies the colors to the row.
    For counter = iFirstCol To iLastCol
        With ActiveSheet.Cells(Target.Row, iFirstCol).Interior
            .ColorIndex = iColor
            .Pattern = xlSolid
        End With
        iFirstCol = iFirstCol + 1
    Next counter

End If

ActiveSheet.Protect
Application.EnableEvents = True

End Sub

Answer 3

Your static rr variable is a Variant and will not have a default value of "". So, when you re-open the file, the cursor will be in the row it was in previously, and because rr is not equal to "" it will not remove the highlight from this line. (In fact, I'm not sure how it is removing the highlight currently.)

Anyway, try:

Static rr
If IsEmpty(rr) Then
    rr = ""
End If

Alternatively, give rr the data-type of Integer or Long, which will assume a default value of 0.

Answer 4

I often highlight rows in tables on selection. While I might be over-simplifying things it seems so much easier then the code you have provided above. Here is what I do; I use just a tiny big of code in the Worksheet selection change for the range that should have the highlighting rows in effect, such as this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("D8:R10000")) Is Nothing Then
Range("B1").Value = ActiveCell.Row
End If
End Sub

Then I use a Conditional formatting for B1 and the range, with any type of formatting you might like for the selected row. A Conditional formatting formula for the above would be: =$B$1=ROW() with an Applied To range of: =$D$8:$R$10000

That's it. No other coding is required and formats can be changed simply. What are your thoughts on this?