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