conditional formatting - row colour/dates

[Camille P]

I'm not understanding conditional formatting re dates.

I have an Excel sheet which has a "Received Date" column. After three days
from the date entered in the "received date" column, I would like the text
for the text for the row to turn orange, after five days I would like the
text for the row to turn red +bold. After 7 days I would like for the rows
in red +bold to show turn yellow and a message box, on document start-up,
to show the user that these rows have overdue tasks that need attention
*now* (basically an irritating nag box)

My first concern is to get the conditional formatting changes working - from
what I've been able to ascertain from online searching has turned up nothing
I've been able to work which leads me to believe I'm not really
understanding what is happening. I'd really appreciate some assistance.

Please let me know if you need more information from me.

Regards
Camille

p.s. I'm working with Win/Office 2000.

 

[Freemini]

To sort out the Conditional formatting your 3 conditions should be:

Condition 1 "Cell value is", "Less than or equal to", =Today()-7
format this with Yellow fill

Condition 2 "Cell value is", "Between", =Today()-5,"and",=Today()-6
format this with Red fill and Bold font

Condition 3 "Cell value is", "Between", =Today()-3,"and",=Today()-4
format this with Orange fill

In order to get a Message you could use the following formula in th
adjacent cell. i.e. if the above conditional formatting applies to cel
B1 then in C1 type the formula

=If(Today()-B1>=7,"PROMPT MESSAGE","")

To make this stand out you could then use Conditional formatting wit
the following condition

Condition 1 "Cell value is", "not equal to", ="" and then format th
cell to become Yellow fill.

Hope this helps

Mik

 

[Max]

Some thoughts on applying the desired conditional formatting (CF):

Assume “Received Date” is in col A, with data in A2 downwards

Select row2

Click Format > Conditional Formatting

Put the settings "Under Condition 1" as :
Formula Is | =AND(TODAY()-$A2>3,TODAY()-$A2<=5)
Click Format button > Font tab > Color > Orange > OK

Click Add >>

Put the settings "Under Condition 2" as :
Formula Is | =AND(TODAY()-$A2>5,TODAY()-$A2<=7)
Click Format button > Font tab > Color > Red > Bold > OK

Click Add >>

Put the settings "Under Condition 3" as :
Formula Is | =TODAY()-$A2>7
Click Format button > Font tab > Color > Red > Bold
Click Pattern tab > Select yellow > OK

Click OK (at the CF dialog box)

Now test out the CF in row2
by inputting various dates in A2 ("Received date" col)
and with some sample data in say, B2, C2, D2, etc
(try input-dates which will test-trigger all 3 conditions)

If you’re happy with the tests,
proceed to copy the CF for row2 to other rows

Select row2 > double click on the Format Painter (yellow brush) icon
(cursor will turn into "a cross + brush")

Just select to "paint over” any other rows etc using the mouse

When done / to cancel, press Escape to revert cursor to normal

 

[Max]

Just some fine tweaks to the 3 CF formulae earlier...
(so that those rows yet unused but with the CF applied,
ie those with blank cells under the "Received date" column
will not be "prematurely" applied with the yellow background color)

Replace earlier formulae with:

Condition 1
=AND(NOT(ISBLANK($A2)),TODAY()-$A2>3,TODAY()-$A2<=5)

Condition 2
=AND(NOT(ISBLANK($A2)),TODAY()-$A2>5,TODAY()-$A2<=7)

Condition 3
=AND(NOT(ISBLANK($A2)),TODAY()-$A2>7)

 

[Camille P]

<snip>

Looking at it with a fresh head this morning I can understand the formula
more clearly. I was hitting my head against the wall because I understood
the If then ElseIf working of the conditions, but I was not specifying
greater than/less than values in each condition. Argh! So simple when you
know!

Thank you both!

Regards
Camille

 

[Max]

Camille, you're welcome!
Glad to hear you got the essence of it.

cheers
Max