multiple conditional formatting rules for the same cell

Copper Contributor


I can't seem to find a formula to help solve this problem. I am creating a Gantt chart that pulls info from multiple sources. I need to find a way to flag a cell when multiple conditional formatting rules are being applied to the same cell.

Here is a small pic of the Gantt Chart:


Conditional formatting rules being applied to row 7:

=AND(E$5>='Data Entry Sheet'!$D6,E$5<='Data Entry Sheet'!$E6)

=NOT(ISBLANK('GEOTECH Schedule'!E$15))


Data Entry Sheet:


Geotech Sheet:


So, basically if the Data Entry Sheet is booking this unit for a particular date and the Geotech schedule is also booking this unit for the same date I need to flag the double booking somehow. Does anyone have any suggestions? 



3 Replies


You could create a third rule that highlights the cell in red and/or places a thick border around the cell. Use this formula that combines the two other formulas:


AND(E$5>='Data Entry Sheet'!$D6,E$5<='Data Entry Sheet'!$E6,NOT(ISBLANK('GEOTECH Schedule'!E$15)))


Make sure that the new rule is listed at the top in the Manage Rules dialog. It will then override the other rules.

@Hans Vogelaar I can't seem to get that third rule to work. Would you have any other suggestions? 


Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?