Thread: Excel Conditional Formatting Question

I am trying to create a spreadsheet to track suspense dates for projects and
I am having difficulty trying to use conditional formatting in order to
complete my project.

1. I want to turn the cell green it is greater than 7 days before the
date entered into the cell.
2. I want to turn the cell yellow if it is within 7 days before the
date entered into the cell.
3. I want to turn the cell red if it is after the date entered into the
cell.



I want to use this in a spreadsheet used to track due dates (suspenses).
Another words, if my suspense is 1 July 2005, I want this suspense block to
be green if it is viewed on 23 Jun 2005 or earlier, yellow if it is viewed
between 24 Jun 2005 and 1 Jul 2005, and red if it viewed on 2 Jul 2005 or
after.



If anyone can help me out with this task, I would really appreciate it.
Thank you.

Joe

One way:

Assume the cell is A1.

CF1: Formula is =(A1-TODAY())>7
Format1: <green>

CF2: Formula is =A1>TODAY()
Format2 <yellow>

CF3: Formula is =A1<=TODAY()
Format3: <red>


Of course, you could reduce the CFs by formatting the cell as green and
only using 2 CFs to turn it yellow or red.


In article <ke-dnfgoE9Jd0VLfRVn-3Q@comcast.com>,
"Joe" <jwhorvathnospam@comcast.net> wrote:

> I am trying to create a spreadsheet to track suspense dates for projects and
> I am having difficulty trying to use conditional formatting in order to
> complete my project.
>
> 1. I want to turn the cell green it is greater than 7 days before the
> date entered into the cell.
> 2. I want to turn the cell yellow if it is within 7 days before the
> date entered into the cell.
> 3. I want to turn the cell red if it is after the date entered into the
> cell.
>
>
>
> I want to use this in a spreadsheet used to track due dates (suspenses).
> Another words, if my suspense is 1 July 2005, I want this suspense block to
> be green if it is viewed on 23 Jun 2005 or earlier, yellow if it is viewed
> between 24 Jun 2005 and 1 Jul 2005, and red if it viewed on 2 Jul 2005 or
> after.
>
>
>
> If anyone can help me out with this task, I would really appreciate it.
> Thank you.
>
> Joe

Thank you very much for your prompt reply. This work great. I also got
another reply that showed me how to do the same thing using the "Cell Value
is" that also worked. I really appreciate your help. It is great to learn
multiple ways to perform the same function. Once again, thank you.
Joe


"JE McGimpsey" <jemcgimpsey@mvps.org> wrote in message
news:jemcgimpsey-12903B.22453308072005@msnews.microsoft.com...
> One way:
>
> Assume the cell is A1.
>
> CF1: Formula is =(A1-TODAY())>7
> Format1: <green>
>
> CF2: Formula is =A1>TODAY()
> Format2 <yellow>
>
> CF3: Formula is =A1<=TODAY()
> Format3: <red>
>
>
> Of course, you could reduce the CFs by formatting the cell as green and
> only using 2 CFs to turn it yellow or red.
>
>
> In article <ke-dnfgoE9Jd0VLfRVn-3Q@comcast.com>,
> "Joe" <jwhorvathnospam@comcast.net> wrote:
>
>> I am trying to create a spreadsheet to track suspense dates for projects
>> and
>> I am having difficulty trying to use conditional formatting in order to
>> complete my project.
>>
>> 1. I want to turn the cell green it is greater than 7 days before
>> the
>> date entered into the cell.
>> 2. I want to turn the cell yellow if it is within 7 days before the
>> date entered into the cell.
>> 3. I want to turn the cell red if it is after the date entered into
>> the
>> cell.
>>
>>
>>
>> I want to use this in a spreadsheet used to track due dates (suspenses).
>> Another words, if my suspense is 1 July 2005, I want this suspense block
>> to
>> be green if it is viewed on 23 Jun 2005 or earlier, yellow if it is
>> viewed
>> between 24 Jun 2005 and 1 Jul 2005, and red if it viewed on 2 Jul 2005 or
>> after.
>>
>>
>>
>> If anyone can help me out with this task, I would really appreciate it.
>> Thank you.
>>
>> Joe