Forum Discussion
Conditional Formatting Formula
I am trying to create a formula for conditional formatting to check that if two individuals have the same day off and neither have coverage, then the rows for those two highlight red. If one does find coverage then the rows stop highlighting.
So in the below example, John and Sarah both have 4/24 and 4/25 off; but Sarah found coverage for the 25th. So I would want the rows for 4/24 to be highlighted and the rows for 4/25 to NOT be highlighted.
2 Replies
If you want the row to highlight only when multiple people have the same day off and no one has coverage, you can try this formula in conditional formatting:
=AND(
COUNTIFS($B$4:$B$100, $B4, $D$4:$D$100, "") > 1,
$D4 = "",
$E4 = ""
)
Basically:
It checks how many people are off on the same day with no coverage.
If it's more than 1 and this specific row also has no coverage listed in either column, it highlights.Should give you what you're going for with the 4/24 vs 4/25 situation. Let me know if it gives you any trouble — happy to tweak it.
Try this formula. A4 or another cell in row 4 should be the active cell when you create the formula.
=AND(COUNTIFS($A$4:$A$1000, "<>"&$A4, $B$4:$B$1000, $B4, $C$4:$C$1000, $C4), COUNTIFS($B$4:$B$1000, $B4, $C$4:$C$1000, $C4, $D$4:$D$1000, "<>")=0)
