时间戳

首页 > 解决方案 > 选择其他活动单元格后保持第一行格式

excel - 选择其他活动单元格后保持第一行格式

问题描述

当前，代码在选择任何不包括第1行的活动单元格时始终选择活动行的“ A”列，并暂时重命名该列A单元格“我的范围”。这就是前几行所实现的，之后我试图突出显示第 2 行之后的任何活动行，并在选择另一个活动行时清除突出显示。

选择另一个活动行时似乎存在问题，因为前一行没有清除颜色。

所以在示例中，我选择了测试 1（第 3 行是活动行）：

在此处输入图像描述

选择另一个活动行（例如测试2（第4行）现在是活动行）时，活动行是颜色的，但先前颜色的行3仍在

在此处输入图像描述

每当我选择另一个活动行时，前一行都不会清除它的颜色，我想在第 1 行保持任何颜色（I1 列中的黑色填充），这就是我希望输出在我选择另一个活动行时的样子，所以在这种情况下，我的测试 1 是活动行（第 3 行），但是当我选择测试 2（第 4 行）作为活动行时，第 3 行会清除它的颜色，如下所示：

在此处输入图像描述

这是我正在运行的整个代码： 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Application.ScreenUpdating = False                                                          'This speeds up the macro by hiding what the macro is doing

        If Target.Row > 1 Then

        ActiveWorkbook.Names.Add Name:="MyRange", RefersToR1C1:=Range("A" & (ActiveCell.Row))   'Defines the name of the ActiveCell as "MyRange" to Autofill VLookup Formula on sheet

        Range("A" & (ActiveCell.Row)).Select                                                    'Always Selects Column A depending on the Active Row selecte

        Dim TR As Variant

        TR = Target.Row > 2

        With TR                                                                                 'With Target refers to the Active Row being selected greater than Row 2

        Target.EntireRow.Interior.ColorIndex = 0                                                'Clears Previous Cells Interior Color

        Target.EntireRow.Interior.Color = RGB(243, 243, 123)                                    'Highlights the entire row that contain the active cell

        End With

        If Target.Address = "$A$2" Then                                                         'Checks if you have selected Row 2 (The comparison row)

            Target.Value = ""                                                                   'If Cell A2 is selected (the "Key" comparison cell from the comparison row) then a blank value is inputted

        Else

            [a2] = ActiveCell                                                                   'Makes cell "A2" equal to the Active Cell value (The "Key" in this case)

        End If

    End If

    Application.ScreenUpdating = True                                                           'Must be "True" after running the code to be able to Read/Write the Workbook

End Sub

标签： excelvbaformat

解决方案

尝试这个。不完全确定您在做什么，但是如果选择了向下第 3 行中的单元格，这应该清除前一行。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Application.ScreenUpdating = False                                                          'This speeds up the macro by hiding what the macro is doing

If Target.Row > 1 Then                                                         'Doesn't Allow the "Titles" in Row 1 to be highlighted or changed
    ActiveWorkbook.Names.Add Name:="MyRange", RefersToR1C1:=Range("A" & (Target.Row))   'Defines the name of the ActiveCell as "MyRange" to Autofill VLookup Formula on sheet
    ActiveSheet.UsedRange.Offset(1).EntireRow.Interior.ColorIndex = 0    
    Target.EntireRow.Interior.Color = RGB(243, 243, 123)                                                                             'With Target refers to the Active Row being selected                                             'Highlights the entire row that contain the active cell

    If Target.Address = "$A$2" Then                                                         'Checks if you have selected Row 2 (The comparison row)
        Target.Value = ""                                                                   'If Cell A2 is selected (the "Key" comparison cell from the comparison row) then a blank value is inputted
    Else
        [a2] = Target                                                                  'Makes cell "A2" equal to the Active Cell value (The "Key" in this case)
    End If

    Me.Range("B2:CK2").Interior.Color = xlNone                                              'Clears any previous (if any) colouring inside cells

    Dim rng As Range                                                                        'Declares variable as a range to store values

    For Each rng In Me.Range("D2:CK2")                                                      'Declares which columns to highlight yellow if there are any parameters in Sheet 2 that vary from Sheet 1
        If IsNumeric(rng.Value) And IsNumeric(Me.Cells(Target.Row, rng.Column)) Then        '[Exludes the Key, Date, Time & Part columns: hence starting at Column D for highlighting variances]
            If rng.Value <> Me.Cells(Target.Row, rng.Column).Value Then                     'Checks if the parameters vary from the main Database ("HE 171")
                rng.Interior.Color = vbYellow                                               'Highlights any varying parameters in Yellow
            End If
        End If
    Next
End If

Application.ScreenUpdating = True                                                           'Must be "True" after running the code to be able to Read/Write the Workbook

End Sub

推荐阅读