Excel 2010 VB脚本 - 突出显示行问题 [英] Excel 2010 VB Script – Highlight Row Issue

问题描述

我想知道有人对此有任何建议。当单击单元格时，我希望行在第6行下方突出显示。所以如果我点击A7，那么第7行将突出显示。如果我点击B9，第7行将突出显示删除，第9行将突出显示。我确实找到了代码，为我所需要的工作，并定制了一点。一切都按照我需要的方式工作，除非Excel保存，关闭和重新打开。

如果第9行突出显示，并且电子表格已保存，关闭并重新打开，第9行将保持高亮显示（即使单击另一个单元格）。所以现在我有2行突出显示。一旦电子表格被打开，为了解决这个问题，请点击不同的行，然后点击第9行。然后它将返回到1个突出显示的行。

<有没有人有这个解决方案？以下是我使用的代码。

感谢任何可以提供的帮助，

Chris

 
 Private Sub Worksheet_SelectionChange（ByVal Target As Range）
 
 
 
 ActiveSheet.Unprotect 
 
 
 
静态rr 
 
 
 
如果rr<>然后
 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 

解决方案

我写了我自己的代码，而不是试图使用我发现的代码。这个效果好多了它还允许用户指定自己的行范围以突出显示。

  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 
 
'''仅调整以下数字以适合您所需的结果'''
 iFirstCol = 1'将此数字更改为需要突出显示的第一列的编号。列A = 1. 
 iLastCol = 15'将此数字更改为需要突出显示的最后一列的编号。列A = 1. 
 iFirstRow = 7'将此数字更改为需要突出显示的第一行的编号。 
 iLastRow = 500'将此数字更改为需要突出显示的最后一行的编号。 
 iColor = 20'更改此数字以使用不同的高亮颜色。 
'''更改结束，请勿更改其他任何内容。'''
 
'仅当所选范围在此if语句标准内时，才会应用行高亮。 
如果Target.Row> iFirstRow  -  1和Target.Row< iLastRow + 1和Target.Column> iFirstCol  -  1和Target.Column< iLastCol + 1然后
 
'当单元格选择更改时，重置全范围内的颜色。 
 ActiveSheet.Range（ActiveSheet.Cells（iFirstRow，iFirstCol），ActiveSheet.Cells（iLastRow，iLastCol））。Interior.Color = xlNone 
 
'将颜色应用于行。 
对于counter = iFirstCol To iLastCol 
 With ActiveSheet.Cells（Target.Row，iFirstCol）.Interior 
 .ColorIndex = iColor 
 .Pattern = xlSolid 
 End With 
 iFirstCol = iFirstCol + 1 
下一个计数器
 
如果
 
 ActiveSheet.Protect 
 Application.EnableEvents = True 
 
 End Sub 
  

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

解决方案

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

这篇关于Excel 2010 VB脚本 - 突出显示行问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！