VBA用户窗体复选框关联条件格式：多条件同时满足时L列格式失效问题及解决

我碰到了一个挺头疼的Excel VBA条件格式问题：单独触发ChiResident或者Apprentice复选框的条件时，格式都能正常生效，但当两个复选框同时勾选时，L列的黄色背景高亮就不显示了。

最开始我写的条件格式代码是这样的：

'Conditional Format for ChiResident & Apprentice
Dim Cond1 As FormatCondition, cond2 As FormatCondition
Set Cond1 = Range("L" & bot_row).FormatConditions.Add(xlExpression, xlEqual, EmployeeInformation.ChiResident.Value = True)
Set cond2 = Range("L" & bot_row & ":AB" & bot_row).FormatConditions.Add(xlExpression, xlEqual, EmployeeInformation.Apprentice.Value = True)
With Cond1
 .Font.Color = vbRed
 .Font.Bold = True
End With
With cond2
 .Interior.Color = vbYellow
End With

为了解决这个问题，我试了好几种写法，但都没搞定：

第一次尝试 #

With Cond1 And Cond2
 Range("L" & bot_row).Font.Color = vbRed And .Font.Bold = True And .Interior.Color = vbYellow
 Range("M" & bot_row & ":AB" & bot_row).Interior.Color = vbYellow
End With

第二次尝试 #

If Cond1 And Cond2 = True Then
 Range("L" & bot_row).Font.Color = vbRed
 Range("L" & bot_row).Font.Bold = True
 Range("L" & bot_row).Interior.Color = vbYellow
 Range("M" & bot_row & ":AB" & bot_row).Interior.Color = vbYellow
End If

第三次尝试 #

'Conditional Format for ChiResident & Apprentice
Dim Cond1 As FormatCondition, Cond2 As FormatCondition, Cond3 As FormatCondition
Set Cond1 = Range("L" & bot_row).FormatConditions.Add(xlExpression, xlEqual, EmployeeInformation.ChiResident.Value = True)
Set Cond2 = Range("L" & bot_row & ":AB" & bot_row).FormatConditions.Add(xlExpression, xlEqual, EmployeeInformation.Apprentice.Value = True)
Set Cond3 = Range("L" & bot_row).FormatConditions.Add(xlExpression, xlEqual, EmployeeInformation.ChiResident.Value = True) And Range("M" & bot_row & ":AB" & bot_row).FormatConditions.Add(xlExpression, xlEqual, EmployeeInformation.Apprentice.Value = True)
With Cond1
 .Font.Color = vbRed
 .Font.Bold = True
End With
With Cond2
 .Interior.Color = vbYellow
End With
With Cond3
 Range("L" & bot_row).Font.Color = vbRed
 Range("L" & bot_row).Font.Bold = True
 Range("L" & bot_row).Interior.Color = vbYellow
 Range("M" & bot_row & ":AB" & bot_row).Interior.Color = vbYellow
End With

最后终于找到了解决方案！问题出在Excel条件格式的StopIfTrue属性上——默认情况下这个属性是True，意思是如果前面的条件满足了，就不会再执行后面的条件格式。只要把两个条件的StopIfTrue都设为False，就能让两个条件同时生效了。

修复后的工作代码：

With Cond1
 .Font.Color = vbRed
 .Font.Bold = True
 .StopIfTrue = False
End With
With Cond2
 .Interior.Color = vbYellow
 .StopIfTrue = False
End With

内容的提问来源于stack exchange，提问作者keef2