Date Overdue function, Macro, or VBS
 

I have a spreadsheet that have two columns: 1. Task 2. Due date
I wanted to have an easy indication to see what tasks (rows) are overdued.
To do so I created two more columns: 3. Today Date 4. Overdue?
In column #3 I typed today() in all rows. In Column 4 I typed: =If (X3X2,
"Overdue","").
In this case whenever I have task which is overdue, automatically field 4 will
read "Overdue"

Can I make a Macro or VB Script that in addition to the above the whole row
will be highlighted (let's say green).

Thanks,

galsaba

)

Hi Galsaba,
Consider using Conditional Formatting.

See Debra Dalgleish's CF tutorials at:

http://www.contextures.com/xlCondFormat01.html

---
Regards,
Norman



"Galsaba" wrote in message
...
I have a spreadsheet that have two columns: 1. Task 2. Due date
I wanted to have an easy indication to see what tasks (rows) are overdued.
To do so I created two more columns: 3. Today Date 4. Overdue?
In column #3 I typed today() in all rows. In Column 4 I typed: =If (X3X2,
"Overdue","").
In this case whenever I have task which is overdue, automatically field 4
will
read "Overdue"

Can I make a Macro or VB Script that in addition to the above the whole
row
will be highlighted (let's say green).

Thanks,

galsaba

)

You could avoid typing the =today() in all those cells and bury it in your
formula:

=if(today()x2,"overdue","")

Then take a look at Format|conditional formatting

You can change the color or the row based on the value in one column.

With rows 2:xxxx selected and row 2 the row with the active cell:
use Formula is:
=$b2="overdue"

(if column B held the warning)


Galsaba wrote:

I have a spreadsheet that have two columns: 1. Task 2. Due date
I wanted to have an easy indication to see what tasks (rows) are overdued.
To do so I created two more columns: 3. Today Date 4. Overdue?
In column #3 I typed today() in all rows. In Column 4 I typed: =If (X3X2,
"Overdue","").
In this case whenever I have task which is overdue, automatically field 4 will
read "Overdue"

Can I make a Macro or VB Script that in addition to the above the whole row
will be highlighted (let's say green).

Thanks,

galsaba

)

--

Dave Peterson

Dave Peterson wrote:
You could avoid typing the =today() in all those cells and bury it in
your
formula:

=if(today()x2,"overdue","")

Then take a look at Format|conditional formatting

You can change the color or the row based on the value in one column.

With rows 2:xxxx selected and row 2 the row with the active cell:
use Formula is:
=$b2="overdue"

(if column B held the warning)


Galsaba wrote:

I have a spreadsheet that have two columns: 1. Task 2. Due date
I wanted to have an easy indication to see what tasks (rows) are
overdued.
To do so I created two more columns: 3. Today Date 4. Overdue?
In column #3 I typed today() in all rows. In Column 4 I typed: =If
(X3X2,
"Overdue","").
In this case whenever I have task which is overdue, automatically
field 4 will
read "Overdue"

Can I make a Macro or VB Script that in addition to the above the
whole row
will be highlighted (let's say green).

Thanks,

galsaba

)

--

Dave Peterson

Dave,

This is great!
How can I make all rows having the same format?

Thanks,

galsaba

Dave Peterson wrote:
You could avoid typing the =today() in all those cells and bury it in
your
formula:

=if(today()x2,"overdue","")

Then take a look at Format|conditional formatting

You can change the color or the row based on the value in one column.

With rows 2:xxxx selected and row 2 the row with the active cell:
use Formula is:
=$b2="overdue"

(if column B held the warning)


Galsaba wrote:

I have a spreadsheet that have two columns: 1. Task 2. Due date
I wanted to have an easy indication to see what tasks (rows) are
overdued.
To do so I created two more columns: 3. Today Date 4. Overdue?
In column #3 I typed today() in all rows. In Column 4 I typed: =If
(X3X2,
"Overdue","").
In this case whenever I have task which is overdue, automatically
field 4 will
read "Overdue"

Can I make a Macro or VB Script that in addition to the above the
whole row
will be highlighted (let's say green).

Thanks,

galsaba

)

--

Dave Peterson

Select all the rows that you care about.

Notice where the activecell is (it should be in the top row if you selected by
dragging down).

Write the formula for that activecell and you should be ok.

====
Alternatively, you could copy|paste special|formats
but that will copy all formatting -- not just the conditional formatting.
(so you might want to verify that the formatting should be the same for all the
rows.)

galsaba wrote:

Dave,

This is great!
How can I make all rows having the same format?

Thanks,

galsaba

Dave Peterson wrote:
You could avoid typing the =today() in all those cells and bury it in
your
formula:

=if(today()x2,"overdue","")

Then take a look at Format|conditional formatting

You can change the color or the row based on the value in one column.

With rows 2:xxxx selected and row 2 the row with the active cell:
use Formula is:
=$b2="overdue"

(if column B held the warning)


Galsaba wrote:

I have a spreadsheet that have two columns: 1. Task 2. Due date
I wanted to have an easy indication to see what tasks (rows) are
overdued.
To do so I created two more columns: 3. Today Date 4. Overdue?
In column #3 I typed today() in all rows. In Column 4 I typed: =If
(X3X2,
"Overdue","").
In this case whenever I have task which is overdue, automatically
field 4 will
read "Overdue"

Can I make a Macro or VB Script that in addition to the above the
whole row
will be highlighted (let's say green).

Thanks,

galsaba

)

--

Dave Peterson

--

Dave Peterson