Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Criteria - Automatic Change Font or Fill Colour
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 |
#2
|
|||
|
|||
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" wrote in message ... 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 |
#3
|
|||
|
|||
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 -- |
#4
|
|||
|
|||
Something like this might help...
=AND(A1=TODAY()+56,A1<TODAY()+84,LEFT(CELL("forma t",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 |
#5
|
|||
|
|||
That's a great move, Dave ! Thanks
-- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Dave Peterson" wrote in message ... Something like this might help... =AND(A1=TODAY()+56,A1<TODAY()+84,LEFT(CELL("forma t",A1),1)="D") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change Font Colour Automatic When Formula Match | Excel Discussion (Misc queries) | |||
Default cell comment fill color and font size | Excel Discussion (Misc queries) | |||
Fill colour a cell from the linked cell | Excel Discussion (Misc queries) | |||
Auto fill color change | Excel Discussion (Misc queries) | |||
Identifying the Active Fill Color | Excel Discussion (Misc queries) |