excel - 非相邻单元格选择的选择十字准线

Question

背景:我有一个大型 Excel 工作表，在其中创建了一个“十字准线”，以便轻松比较与当前选定单元格相同的行和列中的数据。我见过的大多数“十字准线”导航技巧都使用 formatting ，这将删除或覆盖我现有的条件格式。我的解决方法是使用透明线突出显示当前选定单元格的行和列。

问题:该代码适用于大多数选择集，但不相邻的单元格选择除外。对于不相邻的单元格，它只突出显示选择中的第一个单元格。例如:如果我选择 F10然后选择H6 ，我希望有两个十字准线:一个以 F10 为中心，另一个以 H6 为中心.相反，有一个以 F10 为中心的十字准线。 .



问题:有没有办法创建一个适用于非相邻单元格选择的选择十字准线？

当前代码:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

    Dim On_Off As Boolean
    On_Off = True
    If On_Off = False Then Exit Sub

    Dim Sht As Worksheet
    Dim Rng As Range
    Set Sht = ActiveSheet
    Set Rng = Selection
    Dim Shp As Shape
    Dim Clr As Long
    Dim RWt As Double
    Dim CWt As Double
    Dim Trns As Double

    Clr = RGB(100, 20, 180)
    Trns = 0.85
    RWt = Rng.Height
    CWt = Rng.Width

    Debug.Print Rng.Address(False, False, xlA1)

    For Each Shp In Sht.Shapes
        If Shp.Name = "RowLine" Or Shp.Name = "ColLine" Then
            Shp.Delete
        End If
    Next Shp

    With Sht.Shapes.AddConnector(msoConnectorStraight, 0, _
        Rng.Top + Rng.Height / 2, 10000, Rng.Top + Rng.Height / 2)
        .Name = "RowLine"
        .Line.ForeColor.RGB = Clr
        .Line.Transparency = Trns
        .Line.Weight = RWt
    End With

    With Sht.Shapes.AddConnector(msoConnectorStraight, _
        Rng.Left + Rng.Width / 2, 0, Rng.Left + Rng.Width / 2, 10000)
        .Name = "ColLine"
        .Line.ForeColor.RGB = Clr
        .Line.Transparency = Trns
        .Line.Weight = CWt
    End With

End Sub

Answer 1

像这样的东西:

编辑:为多达 3 个不同的区域添加不同的颜色

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

    Dim On_Off As Boolean
    On_Off = True
    If On_Off = False Then Exit Sub

    Dim Sht As Worksheet
    Dim Rng As Range, a As Range, c As Range, i As Long
    Set Sht = ActiveSheet
    Set Rng = Selection
    Dim Shp As Shape
    Dim Clrs
    Dim RWt As Double
    Dim CWt As Double
    Dim Trns As Double

    Clrs = Array(vbRed, vbYellow, vbGreen)
    Trns = 0.85

    For Each Shp In Sht.Shapes
        If Shp.Name Like "RowLine*" Or Shp.Name Like "ColLine*" Then
            Shp.Delete
        End If
    Next Shp

    For Each a In Rng.Areas
        i = i + 1
        Debug.Print a.Address(False, False, xlA1)

        With Sht.Shapes.AddConnector(msoConnectorStraight, 0, _
            a.Top + a.Height / 2, 10000, a.Top + a.Height / 2)
            .Name = "RowLine" & i
            .Line.ForeColor.RGB = Clrs(i Mod 3)
            .Line.Transparency = Trns
            .Line.Weight = a.Height
        End With

        With Sht.Shapes.AddConnector(msoConnectorStraight, _
            a.Left + a.Width / 2, 0, a.Left + a.Width / 2, 10000)
            .Name = "ColLine" & i
            .Line.ForeColor.RGB = Clrs(i Mod 3)
            .Line.Transparency = Trns
            .Line.Weight = a.Width
        End With

    Next a

End Sub