How can you disable and enable conditional formatting ("Strikethrough & Red Text") when vi-CodePudding

I want to disable conditional formatting where the conditions show the row text color red and strikethrough and then enable the row back to conditional formatting once you move to another row. Is this possible without having to delete the formatting and create new?

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

CodePudding user response：

I've edited my earlier (hack-based) reply, as it turns out that the Modify method is simpler to work with: the context for the sample code below is that conditional formatting has been applied in the UI to the B10:F15 range, activated if the first cell on any row has the value Obsolete, if the active cell is within this range, then the conditional formatting for that row will be de-activated:

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