Conditional Formatting Reliant on date and another cell

[splottoman]

Hi,

I am running a project and have a requirement to supply resource to it.

I have a date cell of when resource is required and a cell stating whether the resource has been allocated or not.

There are a couple of rules;

1. If resource is allocated the date cell turns green
2. If the date cell is greater than 7 days before requirement it is amber if no resource is allocated
3. If the date cell is 7 days or less before the resource requirement it is red if no resource is allocated.

I have attached some unformatted samples of how it could look.

If it is easier to format the resource cell rather than the date cell then that will be fine.

Thanks in advance.

Steve

[Aardigspook]

There's no attachment to your post. Here's how to attach:
click Go Advanced (under the reply box),
scroll down until you see Manage Attachments,
click that and select Browse,
select your file and click Open,
click Upload,
(you will see your attachment appear above the Attachment Key)
click Close this window,
click Submit reply.

For your Conditional Formatting, you should be able to use formulae something like these:
=alloc_cell="allocated"
Green
=AND(alloc_cell<>"allocated",date_cell<reqt_cell-7)
Amber
=AND(alloc_cell<>"allocated",date_cell>=reqt_cell-7)
Red

alloc_cell is the cell which states whether the resource is allocated or not
date_cell could be replaced with TODAY() if you want this always to be live
reqt_cell is the cell with the date the resourse is required


If that doesn't make sense, we can have another look once your file is attached.

[splottoman]

Hi Aardigspook,

Thanks for the response.

I have tried to upload the document but its not allowing me to select it for upload for some reason. Never had this problem before?

Thanks for the formulas below. Got them working on my mock spreadsheet but when I go to put them into the spreadsheet I'm actually using.

I will carry on playing around with them.

Cheers
Steve

[Aardigspook]

So, using your attached file, you could colour the whole row for each if you want, using these CF formulae, applied to the range A2:B4:

Please Login or Register  to view this content.

Green fill

Please Login or Register  to view this content.

Amber fill

Please Login or Register  to view this content.

Red fill

The $ signs make the column references absolute, which allows you to colour the whole row. If you'd rather just colour column A, use the same formulae but apply them just to the range A2:A4.

Attached is your file with the above formulae working.
If you still can't get them working in your real file, get back to us with any error messages and/or say what isn't working and hopefully we can help fix the problem.