How can I conditionally format cells based on complex parameters

Question

The days of the year across the top - want the formatting to only reference by day.

There are different titles of training down the left column with various training rooms under each one.

The training rooms repeat.

If anyone claims the room (types info in the cell) for a specific day, all cells for that room on that day to be conditionally formatted. If someone does claim the same classroom, a different format is applied to indicate a conflict.

I have used this formula and it works but becomes too convoluted as I format paint to other columns (using CountA for one formula and OR for another. It seems too manual. Can someone help?

Answer 1

Hi @Darlene Ashley (TBMNC)

Thank you for reaching out on the Microsoft Q&A Forum! 

Based on your description, I tested a similar setup, and it seems to work well. You might want to try these steps: 

Go to Home > Conditional Formatting > New Rule  

• Apply to: Select the full range of cells 
• Format cells where a formula is true: =AND($A2<>"", B2<>"") 
• Click Format, choose a green fill color, and click OK. 

To detect booking conflicts, repeat the same steps with a different formula: 

=COUNTIFS($A$2:$A$100, $A2, B$2:B$100, "<>")>1 

Choose a red fill color to indicate conflict. 

I hope this helps! If the setup differs from what I understood, feel free to share more details, and I’ll be happy to help further.

---

If my answer was helpful, please kindly mark it as accepted so others with the same issue can benefit as well.   

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi,

Share the download link of the Excel file and show the expected result.