Excel- Change Background Color of 2 different cells

Question

So guys, I'm using Excel 2013, and I want to do this :

I have 3 cells:

1. Entry date
2. Color
3. Retrieval parts date.

So:

Entry date will be entered by my for example- 24.10.2016 - and color cell will be filled automatically with green color.

After a while when someone inserts a new retrieval parts date than the color cell will turn red (like it s occupied)

I have done so far: when I insert a date in Entry date cell, then the Color cell will turn green using =$R$4: $R$18 (conditional formatting rules)

And it works but when I insert a new Retrieval date it won't change the color into red.

Thank you very much

Answer 1

You'll need to use two conditional formatting rules and then order them accordingly in the Conditional Formatting Rules Manager (Home -> Conditional Formatting -> Manage Rules).

Conditionally format the second cell using a formula (Home -> Conditional Formatting -> Highlight Cells Rules -> More Rules -> bottom option) where the formula should be =[first cell] <> "" (this is saying that whenever there is a value in the first cell (e.g. A1), apply this rule). Then format as you wish, e.g. background colour green. Then apply a new rule for the same cell, but this time reference the third cell =[third cell] <> "". Set the background colour to red. Now you can define the order of which the rules are applied by moving them up or down in the rules manager. Put the red rule on top and it overrides the green rule when you have data in both cells, but by all means play around with the settings to get familiar to them.