Forum Discussion
How to change the colour of a cell according to the text in a cell that preceeds it.
Hi, I'm looking to add a new quality of life feature to a rota tool I'm using at work to make it easier to see peoples availability. I work for a logistics company with many drivers all with varying shift patterns. due to working time regulations we can not have our employees working a late shift proceeded by an early shift due to the start and finish times.
What I would like to achieve is a condition where say if cell K16 says L (late) and the cell next to it L16 says E (early) it would highlight these two cells say, yellow To make it more visually identifiable to highlight rota errors (and to avoid breaking the law!) I would like to implement this across each row in the document so that it applies to every day in the employees working week.
I'm far from competent with excel so any help for a dumb dumb like me will be a great help.
1 Reply
If you want this to cross the weeks, remove the empty columns between the weeks. You can use an extra thick border between the weeks to distinguish them.
Select the entire range you want to format, say B10:AF23. B10 should be the active cell in the selection.
On the Home tab of the ribbon, select Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula
=OR(AND(A10="L",B10="E"),AND(B10="L",C10="E"))
Click Format...
Activate the Fill tab.
Select yellow.
Click OK, then click OK again.
