Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Conditional Formatting
I am trying to get a column to highlight when the due date is within 7 days of being due. In my case, column H has the formula =G5+(90) whereas column G, line 5 plus 90 days is the due date. I need column H to highlight when the due date is within 7 days and stay that way until a new date is entered into column G. Any ideas would be greatly appreciated! Thanks!
|
#2
|
|||
|
|||
I answered your earlier post where you asked for Column H to be highlighted
when the due date became past due. If you wish, you can combine the conditions, where 7 days before the due date the cell is one color, and when it becomes past due it becomes another color. Here's a copy of the first post: Say your dates start in G2 and go to G100. Select H2 to H100 and then: <Format <Conditional Format Change "Cell Value Is" to "Formula Is", and enter this formula: =AND(G2<"",G2+90<=TODAY()) Now, click on "Format" and choose a Pattern color to your liking. Then <OK <OK. And you should have what you're looking for. However, you may get tired of looking at the colored cells in Column H. You could remove the date from Column G. But, if the date must remain, you might put a time limit on the formatting of Column H. Try this formula instead of the first one: =AND(G1<"",G1+90<=TODAY(),G1+120TODAY()) <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< NOW, to add a format for highlighting the cells 7 days prior to becoming due, *Don't* hit <OK twice after selecting your first format. Hit it once, then click on "ADD" to add a second condition. Again, click "Formula Is", and enter this formula: =AND(G1<"",TODAY()=G1+83,TODAY()<=G1+90) Click on "Format" and add a second, contrasting Pattern color. Now, 7 days prior to the due date the cells should become one color, on the due date change to another color, and if you chose the time limit option, revert to neutral after 30 days. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "tessie0407" wrote in message ... I am trying to get a column to highlight when the due date is within 7 days of being due. In my case, column H has the formula =G5+(90) whereas column G, line 5 plus 90 days is the due date. I need column H to highlight when the due date is within 7 days and stay that way until a new date is entered into column G. Any ideas would be greatly appreciated! Thanks! -- tessie0407 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting Error | Excel Worksheet Functions | |||
difficulty with conditional formatting | Excel Discussion (Misc queries) | |||
conditional formatting question | Excel Discussion (Misc queries) | |||
Determine cells that drive conditional formatting? | Excel Discussion (Misc queries) | |||
Conditional formatting not available in Excel | Excel Discussion (Misc queries) |