Coloring dates

[Imbriani]

I've been told that it's possible in Excel to have dates display in a color using either the IF function of VBA coding. For instance two days from now I need to open a spreadsheet and see that I need to read cultures because the day's date is displayed in red. Is this truly possible? I would imagine it would be something like:

=IF ([G4]=F3+2],[G4]fontcolor=red

Way off, I know, but do you get the idea?

Maybe a better example is to have a spreadsheet when opened displays all the dates 30 days from today in green and all dates 30 days past due in red, etc. etc.

kathy

 

[SkipVought]

Hi,

Use Format/Conditional Formatting...

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[brd24gor]

You could use conditional formatting to do the trick. For example, if you wanted just today's date to be red, setup your conditional format as follows:

1. Highlight the column/row/cells you want formatted
2. Go to Format | Conditional Formatting
3. Choose "Cell Value is"
4. Choose "Equal to"
5. type in =today()
6. Click the format button and choose red text.

If you want to have all dates before todays date, change step 4 accordingly. Play around with it until you get what you want. You can also do it using code, but I find this much easier.

--Brad

 

[Imbriani]

Could I do something like =today()+30 to show a date thirty days ahead in red?

 

[AnotherHiggins]

Imbriani,

You're thinking about it backwards. Today() is dynamic - that is, it is reevaluated every time you refresh or reopen the workbook. So try this:

Use Conditional Formatting. Leave the first field set to "Cell Value is". Change the second field to "Less than or equal to". In the third field, type [COLOR=blue white]=today()-30[/color]. Then choose how you want those cells formatted (use patterns tab for cell shading).

[tt]_____
[blue]-John[/blue]
[/tt][red]"I'm against picketing, but I don't know how to show it."[/red]
-Mitch Hedberg

Help us help you. Please read FAQ181-2886 before posting.

 

[Imbriani]

Thanks so much. I'll give it a try. Switching back and forth between VB and VBA is taxing on the brain!!