Conditional formatting not versatile enough?

February 3, 2011 at 15:34:32
Specs: Windows Server 2003
I'm using Excel 2007, and Column D is for due dates. I want to be able to have a cell go green when it is due in the next 14 days and red when it is due in the next 7 days.

So far I have tried conditional formatting in a number of different ways; using formulas based on cell W1 having today's date, also adding an extra column to show the number of days remaining and then format that column dependant on the value there but I can't even get that to do one colour change properly, let alone two.
If a macro has to be used is there a way to have it automatically run when the file is opened?


See More: Conditional formatting not versatile enough?

Report •

#1
February 3, 2011 at 16:23:54
Try:

go green when it is due in the next 14 days

=IF((D1+14)>=TODAY(),TRUE,FALSE)

red when it is due in the next 7 days

=IF((D1+7)>=TODAY(),TRUE,FALSE)

EDIT: Added section

If your unsure of how to apply:

1) Select your cell

2) On the ribbon click Conditional Formatting

3) Click on New Rules, it’s near the bottom of the dialog box.

4) Click Use Formula to determine which cells to format.

5) Enter the formula: =IF((D1+14)>=TODAY(),TRUE,FALSE)

6) Click on the Format button

7) Select the Fill Tab

8) Select a Green color

9) Click OK

10) Click OK

11) On the ribbon click Conditional Formatting

12) Click on Manage Rules.

13) Click New Rule button.

14) Enter the formula: =IF((D1+7)>=TODAY(),TRUE,FALSE)

15) Click on the Format button

16) Select the Fill Tab

17) Select a Red color

18) Click OK

19) Click OK

MIKE

http://www.skeptic.com/


Report •

#2
February 3, 2011 at 17:00:30
That worked for a single cell, but for the whole column it didn't really work, it seems to have issues with applying a formula to different cells or something
I've decided to use the extra column with the number of days remaining until the due date (i.e. 0=due date, 1=due tomorrow).
My formula for the conditional formatting looks like this
=IF(D1763<=14,TRUE,FALSE) for Green
=IF(D1763<=7,TRUE,FALSE) for Red
Which works in my head, but I can't get it to work properly.

Is it possible to edit cell formatting from within the cell?
If so my formula for formatting for cell D1775 is:
=IF(D1775<=14,IF(D1775<=7,IF(D1775>=0,MAKE_GREEN,""),MAKE_RED))
If this worked I could just copy it to the rest of the column, or can someone write a macro using that logic?

Apologies for this it's been quite a while since I did VB in school and I can't remember the structure properly, and I don't know all the commands, but this is the logic set out semi VB style

Cell.Column=D
Cell.Row=1743

Repeat Until Cell.Row=2000
If Cell.Value >7 And <=14
Then Cell.Background=#00FF00
Else
End
If Cell.Value <=7 And >=0
Then Cell.Background=#FF0000
Else End

Cell.Row=+1
Repeat


My main concern at this stage is that the blank cells (ones that the due date has passed for) will be seen as 0 and made red.

If none of this makes sense I'm sorry, it's Friday.


Report •

#3
February 3, 2011 at 18:09:20
The formulas I gave you worked on my test spread sheet using 20 rows.

Please try it again:

Begin by making sure all your data is entered as valid dates.
To check, highlight your column of dates and format at General, they should all appear as numbers. If they do not, then it's not a valid date.

Next make sure all Old formulas are removed:

1) Select your column of cells, highlight cells D1 through D20

2) On the ribbon click Conditional Formatting

3) Click on Manage Rules, at the bottom of list.

If there are any old formula, delete them by pressing the Delete Rule button.

Once there are no old formulas:

1) Select your column of cells, highlight cells D1 through D20

2) On the ribbon click Conditional Formatting

3) Click on New Rules, third from the bottom of the dialog box.

4) Click Use Formula to determine which cells to format, it is at the bottom of the list.

5) Enter the formula: =IF((D1+14)>=TODAY(),TRUE,FALSE)

6) Click on the Format button

7) Select the Fill Tab

8) Select a Green color, the color you selected should display in the Sample box.

9) Click OK

10) Click OK

I used the following data in column D

        D
 1)  02/03/11
 2)  02/02/11
 3)  02/01/11
 4)  01/31/11
 5)  01/30/11
 6)  01/29/11
 7)  01/28/11
 8)  01/27/11
 9)  01/26/11
10)  01/25/11
11)  01/24/11
12)  01/23/11
13)  01/22/11
14)  01/21/11
15)  01/20/11
16)  01/19/11
17)  01/18/11
18)  01/17/11
19)  01/16/11
20)  01/15/11

If it's been applied correctly, the first 15 rows, from 02/03/2011 to 01/20/2011, should turn Green.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
February 3, 2011 at 18:37:14
I must be doing something wrong. I thought I did it like you said, and they all turned into numbers but now all it is is some numbers in white cells. I even copied it to a new sheet to make sure there was nothing interfering with it but no better.

Any ideas?

Thanks btw


Report •

#5
February 4, 2011 at 12:19:21
they all turned into numbers but now all it is is some numbers in white cells.
That at least tell us there all dates in column D, if you wish, hightlight them again and reformat them as dates.

Using my example dates, put them in column D of a new, blank sheet.

How are you selecting your range of cells?

With your range selection highlighted you select Conditional Formatting off the ribbon/task bar?
(actually in my 2007 the range of cells is not highlighted,
there is just a box around the selected range of cells.)

Now, you tell me the steps your using.

MIKE

http://www.skeptic.com/


Report •

#6
February 8, 2011 at 19:41:49
Ok. I finally got it working!! (Must be in this order)

=IF((D1-TODAY())>0,FALSE,TRUE) as no formatting
=IF((TODAY()+7)<D1,FALSE,TRUE) for Red and
=IF((TODAY()+14)<D1,FALSE,TRUE) for Green,

Thanks heaps for all your help. I wouldn't have been able to do it without you!


Report •

#7
February 9, 2011 at 06:55:27
Glad you got it working.

MIKE

http://www.skeptic.com/


Report •

Ask Question