I'd like to conditionally format a cell based on today's date

I have a column that contains important dates, and I'd like to say make the 
forecolor red if they're 3 months away, blue if their 6 months away, etc...

How do I do this? 

0
8/20/2008 4:32:09 AM
excel 39879 articles. 2 followers. Follow

2 Replies
466 Views

Similar Articles

[PageSpeed] 17

Look up "conditional formatting" in Excel help.
-- 
David Biddulph

"0to60" <holeshot60_nospam@yahoo.com> wrote in message 
news:%23mzT42nAJHA.4052@TK2MSFTNGP06.phx.gbl...
>I have a column that contains important dates, and I'd like to say make the 
>forecolor red if they're 3 months away, blue if their 6 months away, etc...
>
> How do I do this? 


0
David
8/20/2008 4:43:12 AM
On Aug 20, 2:32=A0pm, "0to60" <holeshot60_nos...@yahoo.com> wrote:
> I have a column that contains important dates, and I'd like to say make t=
he
> forecolor red if they're 3 months away, blue if their 6 months away, etc.=
...
>
> How do I do this?

This will set it up for you:

Sub SetUpConditions()

    Range("B2").Select
    With Range("B2")
        .FormatConditions.Delete
        .FormatConditions.Add Type:=3DxlExpression, Formula1:=3D _
        "=3D$B$2>=3DINT(NOW())+180"
        .FormatConditions(1).Font.ColorIndex =3D 2
        .FormatConditions(1).Interior.ColorIndex =3D 5
        .FormatConditions.Add Type:=3DxlExpression, Formula1:=3D _
        "=3D$B$2>=3DINT(NOW())+90"
        .FormatConditions(2).Font.ColorIndex =3D 2
        .FormatConditions(2).Interior.ColorIndex =3D 3
    End With

End Sub

Then have a look at Format -> Conditonal Formatting to tweak it.  Hope
this helps.
0
8/20/2008 4:59:14 AM
Reply:

Similar Artilces:

Track time in HH:min.min(Decimal) format
I'm trying to accumulate elapsed times in a decimal minute format, i.e. 12:00.00 plus 1'30" = 12:01.50. Any ideas? Go with the flow and use hh:mm:ss like everyone else? If not, I would probably put the hours and minutes in separate cells, then combine them with the formula =a1*100+b1, and display with the format ##":"##.## -- Regards, Fred Please reply to newsgroup, not e-mail "Garth Hales" <Garth Hales@discussions.microsoft.com> wrote in message news:7B868D37-DE7B-4F60-B313-891B48E42838@microsoft.com... > I'm trying to accumulate elapse...

Can I copy a table from Word into one cell in Excel without losing data? (Office 97)
I would like to copy a small table from Word into one cell in an Excel worksheet. The first column of the table is a list of numbers. I tried converting the table into text with manual line breaks and tab stops to divide columns and rows, but that didn't solve my problem. Excel pastes the data into several rows. When I try to merge them, I get a warning that the selection contains multiple data values, and merging into one cell keeps the upper-left most data only. What I tried that didn't work: * Formatting the Excel cells as text before pasting the data. * The various options for ...

Query with two date field for Date range
How do I create a Query using two tables that have dates field to calculate a value using both the tables for a Date Range. For Example I have four TABLES- Time Card(TC), PROJECT, TC hours(HOURS), and TC expenses(EXPENSE). TC contains name of employees and their billing rates. PROJECT contains Name of Project. HOURS field are TCId, PROJECTId, Dateworked, and Hours. EXPENSE contains fields TCId, PROJECTId, Dateexpense, Expense. I want to build a query that over a Date Range that sums billable amount=hours worked* billing rate+ expense. I set the Dateworked >=[forms]![Report Date Range]![Be...

How do I transpose Comma Separated Data in each cell and delete t.
I need to switch a names list in each cell: [LAST_NAME, FIRST_NAME] to [FIRST_NAME LAST_NAME] (no comma), preferably into two cells: [FIRST_NAME] [LAST_NAME] Last Name: =LEFT(A1,FIND(",",A1)-1) First Name: =MID(A1,FIND(",",A1)+2,999) HTH Jason Atlanta, GA >-----Original Message----- >I need to switch a names list in each cell: >[LAST_NAME, FIRST_NAME] >to >[FIRST_NAME LAST_NAME] (no comma), >preferably into two cells: >[FIRST_NAME] [LAST_NAME] >. > You may be able to just select the column of names and use Data>Text to Columns. Specif...

I would like to
I would like to have the Sales rep printed on the receipt, how do I accomplish this? Craig, Download the Receipt - 40 column with Sales Rep from here; http://tinyurl.com/4gdbu You must have access to CustomerSource. -- * Get Secure! - www.microsoft.com/security You must be using Outlook Express or some other type of newsgroup reader to see and download the file attachment. If you are not using a reader, follow the link below to setup Outlook Express. Click on "Open with newsreader" under the MS Retail Management System on the right. http://tinyurl.com/75bgz ********** &qu...

Formatting negative numbers #2
I have a text file that gets imported into Excel on a regular basis. The negative numbers come into the spreadsheet in this format: 4000.00- and I want to know if I can write a macro to change all those instances to: -4000.00, moving the negative sign to the proper place. Anyone know if that can be done. In the past we used an old dos application to do that prior to importing it into Excel, but I want to get away from that if we could and just let Excel do it. There are other places in the file that contain text with dashes in it, so I want it only to find the numbers with neg...

Advance Filter can be based on cell Color in Microsoft Excel
In MicroSoft Excel, Advance Filter (Data->Filter->Advance Filter) feature can be enhanced. New feature that we can introduce is to do Advance Filter based on Font/Highlighted Color. e.g. If in excel I have 10 Rows and 3 rows font color is RED and 7 rows font color is GREEN then using Advance Filter option we can apply filter based on Font Color or Highlighted Color. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you ...

Missing import filter for Word! Like "WordPerfect 5.1 (UNIX)" ...
I'm missing much import filter on Office-Mac (specially WordPerfect 5.1 UNIX for Word.mac). Office 95/97/2000/XP for windows have much more useful import filter. Why all this filter are missing in Office.mac????? Or are all them included in an additional package????? Hi Dieter, This question has been answered over and over again in the Macintosh Word newsgroup. Take a gander in that newsgroup for your answer. Office does not ship with a converter because a converter has not been built that does a good job with converting. Details are here: http://www.mvps.org/word/FAQs/General/WordP...

Conditional formating an entire row vs. only one cell
I have 400 rows of data. There are four columns that contain any one of five different key letters (R, NR, O, U, NA). What I want to do is that everytime a letter such as NR shows up in any of the four columns, the entire row has a conditional format applied. When I do "Cell Is", the conditional format is only applied to the one cell that meets the condition even if I have the entire row(s) highlighted when I create the conditional format. I tried to find similar posts to help but have not been successful. Using Excel 2003. Highlight all your data (assuming you have start...

Null value in multiple condition formula
Hi, I wonder if anybody out there could tell me how to do this? I am wanting to count the number of times in my worksheet a cell in column N is empty, WHEN the value of column C is "C" AND the value of column H (which contains dd/mm/yyyy data) is within the month of January. I am using the formula: =SUMPRODUCT((C2:C100="C")*(MONTH(H2:H100)=1)) to count those cells where Column C = "C" AND the month in column H = Jan (thanks to you wise and helpful people on the forum, I might add) but cannot work out how to do the null value in column N bit! Any help w...

Extracting rows from a file based on another
Hi, I have 2 excel files where on is a subset of the other. All rows are uniquely identified by a number. I would like to automatically extract the rows in the subset from the superset, resulting in a 3rd file with the difference between the two, like a batch operation. Do I need a utility for this or is there an option in Excel that allows me to do this? Cheers, Thorbjorn Hi I posted you a links as starting point in the German newsgroup. Please post back if you have fruther question (best would be with some example rows in plain text - no attachment please) >-----Original Message-...

Unique entries based on condition
Hi, I'm really struggling with what I thought should be easy. A B C D branch salesperson transaction num Item code I'm trying to the number of unique saples people in each branch, the number of unique tranactions by each sales person, and the number of unique items in each transaction. In another program I use "Count Distinct" but am struggling to acheieve the same in excel. Any ideas? -- Rich http://www.rhodes-lindos.co.uk http://www.rhodes-pefkos.co.uk Try these array fo...

How to change default date format of dd-mmm?
When I enter a date in m/dd format, e.g. "7/15",it is automatically converted to dd-mmm format ("15-Jul"). How can I change this so it stays in 7/15 format? Thanks. Ken Format the cell as m/dd from Format>Cells and click on Date and m/dd Andy. "Ken B." <kborthwick@movadogroup.com> wrote in message news:004c01c34add$96470350$a101280a@phx.gbl... > When I enter a date in m/dd format, e.g. "7/15",it is > automatically converted to dd-mmm format ("15-Jul"). > How can I change this so it stays in 7/15 format? > Thanks. Ken...

copy conditional formatting #2
I can set up a conditional format in one cell. I would like to copy that CF to all the cells in the column. Any way to do that in 2007? You can use the format painter to copy and paste all formats including conditional formatting -- Regards, Peo Sjoblom "Byrdie Ethel Pillinger" <ByrdieEthelPillinger@discussions.microsoft.com> wrote in message news:2CE3D7DF-60A1-47E0-B12F-91B88CC606E0@microsoft.com... >I can set up a conditional format in one cell. I would like to copy that >CF > to all the cells in the column. Any way to do that in 2007? Might be e...

Macro Help
I need a macro that will insert a number of rows based on the number of months between a start and end date and then copy the information in the above row to the new rows. The records are over 8,350.. so ill also need some idea how to get it to stop when it fills the worksheet so i can transfer those into another workbook What version of Excel are you using? "Katerinia" <Katerinia@discussions.microsoft.com> wrote in message news:394E5E6B-C761-4D83-87BC-B0A88AA41BAF@microsoft.com... >I need a macro > that will insert a number of rows > based on the...

Making Excel act like a database.
I need some help on a common problem in our company. We have several clients that we have agreed to do some Excel 2003 spreadsheets for. The spreadsheets analyze our performance for them. These spreadsheets follow a very specific formatting that the clients have gotten use to and are unwilling to change. When we have to update the spreadsheets once a month, it can take all day to do the updating for each client because we are currently using copy and paste to get the data in the right cells. I'll explain more in a second, but what I am hoping to find out from this posting is alternate...

Transaction Reconciliation Date
Is there any way that I can see during what bank rec period a particular transaction cleared. Right now any report I configure will only tell me the cleared status (R or C) but not the month in which it cleared. I would like to be able to tell a payee when a check cleared without having to pull out all my bank statements. Thanks ...

COMBINING CELLS WITH LIKE DATA
I HAVE A SPREADSHEET TO TRACK INTERNAL MACHINING SCRAP SIMILAR TO THE ONE BELOW. I AM LOOKING FOR A WAY TO INSTANTANIOUSLY COMBINE ALL THE TOTALS FOR CUSTOMER A OR PART 13112 OR MACHINE B9 ON ANOTHER SPREADSHEET IF ANYONE KNOWS OF A WAY TO DO THIS PLEASE LET ME KNOW. THANKS. DATE CUSTOMER PART MACH # QTY TOTAL 8/9/2004 A 111 B9 2 13.46 8/9/2004 S 123 B9 4 26.92 6/4/2004 S 123 C14 5 31.95 6/1/2004 M 2131 C14 12 29.88 7/22/2004 B 13123 C3 1 0.75 7/5/2004 A 12312 C4 1 0.75 7/5/2004 ...

How to convert Conditional Format into the "real" format?
Hi, does anybody know the trick to easily convert Conditional Formatting into the "real" cell format? (don't need to have conditional format anymore) Thanks Select your cells. Choose Format/Conditional Formatting... and click Delete. In article <ugg2DVbJEHA.556@TK2MSFTNGP10.phx.gbl>, "Arie Sukendro" <info@NOSPAMdrsirx.com> wrote: > Hi, > does anybody know the trick to easily convert Conditional Formatting into > the "real" cell format? (don't need to have conditional format anymore) > Thanks This will remove the conditiona...

How do I keep Excel chart format/color in PowerPoint?
This is a PowerPoint question - sorry if it's in the wrong posting. I am inserting a chart in a PP presentation & the color doesn't match the linked Excel file. What do I do? On Thu, 13 Aug 2009 07:56:02 -0700, EC <EC@discussions.microsoft.com> wrote: >This is a PowerPoint question - sorry if it's in the wrong posting. I am >inserting a chart in a PP presentation & the color doesn't match the linked >Excel file. What do I do? You could try one of the Excel or Powerpoint groups, this one is for Visio. -- Regards, Paul Herber, Sandrila Ltd. Bow t...

Multiple results of Vlookup in one cell
Dear Experts: How can I retrieve multiple occurances of lookedup value in one cell?? I want to retrieve thru formula multiple product sales in a given date. Jan-1 Product 1 Feb-15 Product 4 Jan-5 Product 3 Feb-15 Product 1 Feb-15 Product 2 so results should be : Lookup value: Feb-15 Results: "Product 4, Product 2, Product 1" as a string in one cell Is it possible using a Vlookup or any other combination of formula Please help Murtaza Hi Murtaza, Look here to get multiple results: http://office.microsoft.com/en-us/excel/HA012260381033.aspx Then you...

Dates #6
Cell D14 contains a date with format DD/MM/YY I want to split it into the cells E14, F14, G14 Where E14 contains the DD part, F14 containes the MM part and G14 contains the YY parts. What formula should I use? =Day(D14) =Month(D14) =Year(D14) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace@mailinator.com with @tiscali.co.uk "Khalil Handal" <khhandal@stthom.edu> wrote in message news:eRSZnRTSHHA.5060@TK2MSFTNGP06.phx.gbl... > Cell D14 contains a date with format DD/MM/YY > I want to split it...

My Headers are Spam Like
I have an Exchange 5.5 server on NT domain, the headers on messages say: received from <machine name>.<domain name> which is SERVERA.COMPANYB, and some sites are rejecting our mail as spam. I think it's a dns problem because SERVERA.COMPANYB doesn't resolve to anything. Is there any way I can get a .COM into that header? dlw wrote: > I have an Exchange 5.5 server on NT domain, the headers on messages > say: received from <machine name>.<domain name> which is > SERVERA.COMPANYB, and some sites are rejecting our mail as spam. > I think it's a...

Conditional Formatting if the field contains a specific number
I am working in Access 2003 and have created a database of chefs who volunteer for our annual event. Over the years, several are repeat volunteers. I am attempting to create a report wherein the "year" field will turn yellow if the information contains the year "2010". Can this be done (and by someone of my limited grasp of Access)? Thank you for any help you can give. nbslarson, Have you looked at Conditional Formatting? Look under Format on your menu bar. -- Gina Whipp 2010 Microsoft MVP (Access) "I feel I have been denied critical, nee...

formatting worksheet tabs
i would like to format my worksheet tabs with different colors & fonts. is there a way to do this in excel 2k? i thought this option was available in the older versions? thanks for any help terri tilghma Terri Only in 2002 and 2003. Earlier versions do not allow, no how. Gord Dibben Excel MVP On Tue, 3 Feb 2004 12:16:05 -0800, "terri tilghman" <anonymous@discussions.microsoft.com> wrote: >i would like to format my worksheet tabs with different colors & fonts. is there a way to do this in excel 2k? i thought this option was available in the older versions?? &...