在ExcelVBA中查看突出顯示的選定行文本時，如何禁用和啟用條件格式（“洗掉線和紅色文本”）？

我想禁用條件格式，其中條件顯示行文本顏色為紅色和洗掉線，然后在移動到另一行后啟用該行回到條件格式。這是否可能無需洗掉格式并創建新格式？

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

        Application.EnableEvents = False
        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual

        Static xRow
        Static xColumn

        If xColumn <> "" Then
            With Columns(xColumn).Interior
                .Color = RGB(38, 38, 38) 'dark grey
                .Pattern = xlSolid
            End With
            
            Range("$A$3:$A2000").Font.Color = vbYellow
            Range("$C$3:$C2000").Font.Color = vbYellow
            Range("$B$3:$B2000").Font.Color = vbWhite
            Range("$D$3:$L2000").Font.Color = vbWhite
            Range("$A$1:$L$1").Interior.Color = RGB(38, 38, 38) 'dark grey
            Range("$A$2:$L$2").Interior.Color = vbBlack
            
            
            
            With Rows(xRow).Interior
                .Color = RGB(38, 38, 38) 'dark grey
                .Pattern = xlSolid
            End With
            
            Range("$A$3:$A2000").Font.Color = vbYellow
            Range("$C$3:$C2000").Font.Color = vbYellow
            Range("$B$3:$B2000").Font.Color = vbWhite
            Range("$D$3:$L2000").Font.Color = vbWhite
            Range("$A$3:$L2000").Interior.Color = RGB(38, 38, 38) 'dark grey
        End If

        pRow = Selection.Row
        pColumn = Selection.Column
        xRow = pRow
        xColumn = pColumn
        
        With Columns(pColumn).Interior
            .Color = RGB(89, 89, 89) 'light grey
            .Pattern = xlSolid
        End With
        
        With Columns(pColumn).Font
             .Color = vbBlack
        End With
        
        With Columns(pColumn).Font
             .Bold = True
        End With
        
        With Columns(pColumn).Font
             .Italic = True

             '### I have tried this and does not work
              '.Strikethrough = False
        End With
        
        With Rows(pRow).Interior
            .Color = RGB(89, 89, 89) 'grey
            .Pattern = xlSolid
        End With
        
        With Rows(pRow).Font
            '.Color = RGB(255, 255, 255) 'white
            '.Color = RGB(38, 38, 38) 'dark grey
            '.Color = RGB(0, 255, 0) 'green
            .Color = vbGreen
            '.Strikethrough = False
        End With
        
        With Rows(pRow).Font
            .Bold = True
        End With
        
        With Rows(pRow).Font
            .Italic = True
        End With
        
        '## This works, but I really do not want to use it if I have to.
        ''Delete Previous Conditional Formats
        'Rows(pRow).FormatConditions.Delete
        
        Selection.Cells.Font.Color = RGB(255, 0, 102) 'pink
        Selection.Cells.Interior.Color = RGB(38, 38, 38) 'dark grey
        

        '## Tried this and does not work as well.
        'Selection.Rows.Font.Strikethrough = False

        Range("$A$2:$L$2").Font.Color = RGB(0, 176, 245) 'light blue
        Range("$A$1:$L$1").Font.Color = vbYellow
        
        Application.Calculation = xlCalculationAutomatic
        Application.ScreenUpdating = True
        Application.EnableEvents = True
End Sub

uj5u.com熱心網友回復：

我編輯了我之前的（基于黑客的）回復，因為事實證明該Modify方法更易于使用：下面示例代碼的背景關系是條件格式已在 UI 中應用于 B10:F15 范圍，如果任何行的第一個單元格的值為Obsolete，則激活，如果活動單元格在此范圍內，則該行的條件格式將被停用：

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim cfRange As Range, cfCondition As String
    Set cfRange = Range("B10:F15")
    cfCondition = "=$B10=""Obsolete"""
    If Not Application.Intersect(Target, cfRange) Is Nothing Then
        Dim newCondition As String
        newCondition = Strings.Replace(cfCondition, "=$", "=AND($")
        newCondition = newCondition & ", ROW()<>" & Target.Row & ")"
        cfRange.FormatConditions(1).Modify xlExpression, , newCondition
    Else
        cfRange.FormatConditions(1).Modify xlExpression, , cfCondition
    End If
End Sub

標籤：擅长 vba

上一篇：如果作業表名稱匹配，則將資料從資料作業簿傳輸到目標作業簿

下一篇：根據單元格資料在單元格上創建檔案夾和子檔案夾以及超鏈接