Conditional Formatting a range of cells based on a range of cells on another sheet.

[operationsahcc]

I am working on an employee schedule for a bar and restaurant that operate within a hotel. The staff will work between the two locations, meaning they can either be scheduled in the bar or the restaurant. The locations are labeled "Lounge" and "Dining Room". Each location has its own sheet, and all Employees are listed in both with their hours posted. It is the exact same (I copied and pasted the entire schedule onto another sheet and changed the colour of the top and the name). What I am trying to do is find a way for each sheet to reference the other sheet and conditionally highlight a cell if an employee has been double scheduled. For example, I have "Adam" working in the Dining Room on Monday from 3:00PM to 3:30PM, I would like the respective cells in the Lounge schedule to be highlighted red if I try to input any hours for him on that same day.

I have successfully done this as an example, however, with this method I have only been able to do this cell by cell, and I'm hoping there is a way to apply the formatting to all the employees for all days.



Screenshot 2020-11-12 205032.png
Screenshot 2020-11-12 205122.png
Screenshot 2020-11-12 205154.png

[6StringJazzer]

The strategy is to use a rule that just looks at one cell (instead of two), use relative addressing instead of absolute, and then apply the same rule to all cells on the sheet that have hours. See attachment.

[kvsrinivasamurthy]

Instead of CF it is better to go for Data Validation. It will prevent the entry itself. It will be easy for any further calculation.
Formula for data validation of F8 in "Dining Room" Sheet

=Lounge!F8=""

Copy validation other required cells.

Formula for data validation of F8 in "Lounge" Sheet

='Dining Room'!F8=""

Copy validation other required cells.

Error message is given.