Criteria - Automatic Change Font or Fill Colour

[stevembe]

Hi all (posted in another forum incorrectly, sorry!) . I have used some date formula in my worksheet:

=TODAY()+56
=TODAY()+84
=TODAY()+98

What I want to do is colour the font with the date if it meets the criteria above. E.g. if I were to type anywhere on the worksheet a date 55 days from today it would automatically turn red. If I were to type in a date 80 days from today it would turn Blue etc

Also, if this can be done for the fill of the cells even better. Many thanks in advaance and enjoy the day.

[Max]

One way ..

Select entire sheet (with A1 active)

Click Format > Conditional Formatting

Set it for conditions 1 - 3 as :

Formula | is:
Cond1: =AND(A1>=TODAY()+56,A1<TODAY()+84)
Cond2: =AND(A1>=TODAY()+84,A1<TODAY()+98)
Cond3: =AND(A1>=TODAY()+98,A1<>"",ISNUMBER(A1))

Formats*
Cond1: Red fill / white font / bold
Cond2: Blue / default font
Cond3: Yellow / default font

*Click "Format" button & change it to-your-taste
in the patterns tab and font tab

Click OK at the main dialog

Test it out ...
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"stevembe" <[email protected]> wrote in
message news:[email protected]...
>
> Hi all (posted in another forum incorrectly, sorry!) . I have used some
> date formula in my worksheet:
>
> =TODAY()+56
> =TODAY()+84
> =TODAY()+98
>
> What I want to do is colour the font with the date if it meets the
> criteria above. E.g. if I were to type anywhere on the worksheet a date
> 55 days from today it would automatically turn red. If I were to type in
> a date 80 days from today it would turn Blue etc
>
> Also, if this can be done for the fill of the cells even better. Many
> thanks in advaance and enjoy the day.
>
>
> --
> stevembe
> ------------------------------------------------------------------------
> stevembe's Profile:
http://www.excelforum.com/member.php...o&userid=27743
> View this thread: http://www.excelforum.com/showthread...hreadid=472517
>

[Max]

Note that since dates are numbers in Excel, the CF would also
(unfortunately?) be triggered by numbers which are of the same order as the
dates input. For example, 1st Jan 2006 is 38718, 2nd Jan 2006 is 38719,
and so on. I'm not sure whether there's a way around this limitation using
normal CF. Anyway, do be aware of this.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

[Dave Peterson]

Something like this might help...

=AND(A1>=TODAY()+56,A1<TODAY()+84,LEFT(CELL("format",A1),1)="D")



Max wrote:
>
> Note that since dates are numbers in Excel, the CF would also
> (unfortunately?) be triggered by numbers which are of the same order as the
> dates input. For example, 1st Jan 2006 is 38718, 2nd Jan 2006 is 38719,
> and so on. I'm not sure whether there's a way around this limitation using
> normal CF. Anyway, do be aware of this.
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --

--

Dave Peterson

[Max]

That's a great move, Dave ! Thanks
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> Something like this might help...
>
> =AND(A1>=TODAY()+56,A1<TODAY()+84,LEFT(CELL("format",A1),1)="D")