VBA Conditional Format if both conditions met

keef2 0
Viewed 27

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:

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. Basically do the same thing but highlight whole range in yellow and have bold red text in column L.

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:

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
conditional-formatting vba excel
1 Answers

This does not answer the question of the author entirely, as some information is missing, but it shows a way of working in order to solve the issue.

I would advise you to check the conditional formatting rules, as your VBA macro has created them. And even then, you might be faced with a bug, like in my case:

In order to check how conditional formatting works with different rules, I have created conditional formatting with two rules, who are both correct (the value of "B2" equals 5, and I use the formulas =B2>1 and =B2<8. Nevertheless the second rule never gets applied, which look like a bug, as you can see here:

enter image description here

(Although the checkbox "Stop if True" is not clicked, it looks like the conditional formatting stops after the first rule.)

So: check your rules, using the Conditional Formatting Rules Manager, and only if this is not clear, you can suspect a bug in the conditional formatting feature.

Dominique0
Related