VBA Condtional Format Assistance

[keef2]

Hey there:

Conditional Format Code is working for each condition individually, but not if both are conditions are met. If both Conditions are met the only issue is column L doesn't highlight yellow:

'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, x1equal, EmployeeInformation.Apprentice.Value = True)
With Cond1
.Font.Color = vbRed
.Font.Bold = True
End With
With cond2
.Interior.Color = vbYellow
End With

I have tried a few ways to get this to work if both conditions are met but cannot get this to work appropriately. Any help is much appreciated here are my attempts:

Attempt 1:

  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

Attempt 2:

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

Attempt 3:

    '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, x1equal, 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, x1equal, 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

 

[Dan_W]

After running your code (and setting the rules), when you look at the Conditional Formatting Rules Manager window, does one of the rules have the Stop If True checkbox checked? That might explain it.

 

[keef2]

After running your code (and setting the rules), when you look at the Conditional Formatting Rules Manager window, does one of the rules have the Stop If True checkbox checked? That might explain it.

View attachment 73727

Thanks! Thats exactly what it was. Here is updated code to take care of this:

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
    .StopIfTrue = False
    End With
    With Cond2
    .Interior.Color = vbYellow
    .StopIfTrue = False
    End With

 

[Dan_W]

That's great. Thanks for posting the code solution - saves me having to remind myself what the exact object/property name was...