向工作表事件添加参数

Question

我发现了一个宏，它可以很好地突出显示与所选单元格对应的整个行(无论选择哪个单元格，宏都会运行，并突出显示所有行)。当前的迭代有一些缺点，我似乎找不到一种方法来制定参数，我希望将突出显示限制在特定的行数。什么好主意吗?

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Const cnNUMCOLS As Long = 512
    Const cnHIGHLIGHTCOLOR As Long = 6  'default lt. yellow
    Static rOld As Range
    Static nColorIndices(1 To cnNUMCOLS) As Long
    Dim i As Long

    If Not rOld Is Nothing Then 'Restore color indices
        With rOld.Cells
            If .Row = ActiveCell.Row Then Exit Sub 'same row, don't restore
            For i = 1 To cnNUMCOLS
                .Item(i).Interior.ColorIndex = nColorIndices(i)
               Next i
        End With
    End If
    Set rOld = Cells(ActiveCell.Row, 1).Resize(1, cnNUMCOLS)
    With rOld
        For i = 1 To cnNUMCOLS
            nColorIndices(i) = .Item(i).Interior.ColorIndex
        Next i
        .Interior.ColorIndex = cnHIGHLIGHTCOLOR
    End With
End Sub

Answer 1

试一下:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Const cnNUMCOLS As Long = 512
    Const cnHIGHLIGHTCOLOR As Long = 6  'default lt. yellow
    Static rOld As Range
    Static nColorIndices(1 To cnNUMCOLS) As Long
    Dim i As Long, bClear as Boolean, bInRange as Boolean
    bInRange = Not Application.Intersect(Me.Range("11:54"), Target) Is Nothing
    bClear = Not Application.Intersect(Me.Range("A6"), Target) Is Nothing
    'exit if selection is not in the first ten rows or A6
    If Not (bClear Or bInRange) Then Exit Sub
    If Not rOld Is Nothing Then 'Restore color indices
        With rOld.Cells
            If .Row = ActiveCell.Row Then Exit Sub 'same row, don't restore
            For i = 1 To cnNUMCOLS
                .Item(i).Interior.ColorIndex = nColorIndices(i)
               Next i
        End With
    End If
    If Not bInRange Then
        Set rOld = Nothing 
        Exit Sub  ' Exit if we're in A6
    End If
    Set rOld = Cells(ActiveCell.Row, 1).Resize(1, cnNUMCOLS)
    With rOld
        For i = 1 To cnNUMCOLS
            nColorIndices(i) = .Item(i).Interior.ColorIndex
        Next i
        .Interior.ColorIndex = cnHIGHLIGHTCOLOR
    End With
End Sub

注意:您的代码假设只选择了一个单元格-如果用户在>1行中选择多个单元格