如何在Excel VBA中查看突出显示的选定行文本时禁用和启用条件格式("Strikethrough & Red Text")？

Question

我想禁用条件格式，其中条件显示行文本颜色为红色和取消线，然后在移动到另一行时使行返回条件格式。这可能不需要删除格式并创建新的吗?

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

Answer 1

我已经编辑了我之前的(基于黑客的)回复，因为事实证明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