代码之家 › 专栏 › 技术社区 › MTT

避免突出显示不打算

vba excel

MTT · 技术社区 · 6 年前

我有以下代码以绿色突出显示用户单击的行。在代码的末尾,我选择单元格B6来取消选择整行并将所选内容移到上面。它运行良好,除了事实上,细胞B6也被突出显示为绿色,我不希望那样我怎样才能去掉它?

在工作表中,我有:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    'If not in the table, exit sub
    If Intersect(Range("Schema"), ActiveCell.EntireRow) Is Nothing Then Exit Sub

    Call ClickInside

End Sub

在模块中:

Sub ClickInside()

    Dim cellno As String: cellno = Str(ActiveCell.row)
    Dim myRow As Range
    Set myRow = ActiveCell.EntireRow 'I want to select the row in the table ONLY

    Call Unprotect_table
    Call MarkRow(cellno, myRow)
    Call Protect_table
End Sub

Sub MarkRow(cellno As String, myRow As Range)

    'Marking that row in green
    Range("Schema").Interior.ColorIndex = 0
    Range("B" & Trim(cellno) & ":I" & Trim(cellno)).Select
    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .Color = RGB(0, 255, 0)
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Application.ScreenUpdating = False

    'Show above
    If Not myRow Is Nothing And CLng(cellno) >= 9 Then
        Range("EditCountry").Value2 = ThisWorkbook.ActiveSheet.Range("B" & Trim(cellno)).Value2
        Range("EditNodeName").Value2 = ThisWorkbook.ActiveSheet.Range("C" & Trim(cellno)).Value2
        Range("EditNodeId").Value = ThisWorkbook.ActiveSheet.Range("D" & Trim(cellno)).Value2
        Range("EditParentNode").Value = ThisWorkbook.ActiveSheet.Range("E" & Trim(cellno)).Value2
        Range("EditParentNodeId").Value = ThisWorkbook.ActiveSheet.Range("F" & Trim(cellno)).Value2
        Range("EditActive").Value = ThisWorkbook.ActiveSheet.Range("G" & Trim(cellno)).Value2
        Range("EditFrom").Value = ThisWorkbook.ActiveSheet.Range("H" & Trim(cellno)).Value2
        Range("EditTo").Value = ThisWorkbook.ActiveSheet.Range("I" & Trim(cellno)).Value2
    End If

    'Move selection
    Range("B6").Select

End Sub

谢谢!

1 回复 | 直到 6 年前

FunThomas 6 年前

你的陈述 Range("B6").Select 再次触发事件例程。要防止这种情况,请使用以下语句 Application.EnableEvents = False :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Range("Schema"), ActiveCell.EntireRow) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Call ClickInside
    Application.EnableEvents = True
End Sub

不过,也许你可以看看其他的解决方案 条件格式 ,例如 https://stackoverflow.com/a/22350417/7599798