One way to do it would be with a custom formula conditional format rule per colour. Instructions for adding a custom rule to conditional formula are available here. To summarize:
- Open a spreadsheet.
- Select the cell or range of cells that you want to apply formatting rules to.
- Click the Format menu.
- Select Conditional formatting.
- Under the “Condition” drop-down menu, choose Custom formula is.
- Add in the relevant formula and rules.
- Click Done.
You need to add the most restrictive rule first. Rules will be evaluated in order, so the first one that matches will determine the format to be applied. In your example, this means that you would need to add the red rule first followed by the green one.
In the example you gave, the range ("Apply to range" field) for all formulas would be
The custom formula for the red rule would be:
=IF(EQ(A1, "Open"), IF(C1 <= TODAY() + 7, true, false), false).
This would apply the format to all dates which are within 7 days of today.
If you wanted to add an intermediate orange rule for dates within 28 days for example, the formula would be:
=IF(EQ(A1, "Open"), if(C1 <= TODAY() + 28, true, false), false).
The final green rule formula would be:
=IF(EQ(A1, "Open"), if(C1 > TODAY() + 28, true, false), false).
You may be able to simplify the number of rules or complexity of the formulae, but the above should work.