+ Reply to Thread
Results 1 to 12 of 12

Conditional Formatting Using If Statement On Opening Of Workbook

  1. #1
    Bill Foley
    Guest

    Conditional Formatting Using If Statement On Opening Of Workbook

    Hey gang,

    I was wondering if anyone had a solution for this EXCEL query:

    I have several worksheets in a workbook that has dates of completion of
    various training topics. Some of these topics require annual (12 months),
    bi-annual (24 month), 6-month, or even 2-month re-training.

    What I am trying to accomplish is to automatically shade all cells for dates
    that fall within one month of these periodicities based on the computer's
    clock when a workbook is opened. Unfortunately different columns have
    different periodicities so what I was planning on doing was putting that
    monthly number in a cell ("12" in A1000, for example for all Column A
    values). When the file is opened, if any of the dates in column A are more
    than 11 months from today's date (A1000 minus 1), they are within one month
    of needing to be re-trained and I want the cell shading to be a light
    yellow.

    Can this be done using Conditional Formatting without VBA or do I need
    something to be run based on an AutoOpen macro?

    TIA!

    --
    Bill Foley
    www.pttinc.com
    Microsoft PowerPoint MVP



  2. #2
    bpeltzer
    Guest

    RE: Conditional Formatting Using If Statement On Opening Of Workbook

    You should be able to do that with conditional formatting. Set the condition
    to read 'cell value is less than' and the value to compare to is
    =TODAY()-$A$1000, with the value in $A$1000 set to the number of days allowed
    between retraining less the number of days advance notice desired. You could
    set it up so that you've got multiple conditions with different periods to
    create shades showing greater urgency, but be sure that you test for the
    'most urgent' first.

    "Bill Foley" wrote:

    > Hey gang,
    >
    > I was wondering if anyone had a solution for this EXCEL query:
    >
    > I have several worksheets in a workbook that has dates of completion of
    > various training topics. Some of these topics require annual (12 months),
    > bi-annual (24 month), 6-month, or even 2-month re-training.
    >
    > What I am trying to accomplish is to automatically shade all cells for dates
    > that fall within one month of these periodicities based on the computer's
    > clock when a workbook is opened. Unfortunately different columns have
    > different periodicities so what I was planning on doing was putting that
    > monthly number in a cell ("12" in A1000, for example for all Column A
    > values). When the file is opened, if any of the dates in column A are more
    > than 11 months from today's date (A1000 minus 1), they are within one month
    > of needing to be re-trained and I want the cell shading to be a light
    > yellow.
    >
    > Can this be done using Conditional Formatting without VBA or do I need
    > something to be run based on an AutoOpen macro?
    >
    > TIA!
    >
    > --
    > Bill Foley
    > www.pttinc.com
    > Microsoft PowerPoint MVP
    >
    >
    >


  3. #3
    Bob Phillips
    Guest

    Re: Conditional Formatting Using If Statement On Opening Of Workbook

    Bill,

    You should be able to do it without VBA .

    When you say periodicity, will you enter a date like 1st Aug 2000, and need
    it coloured in Jul 2001, Jul 2002, Jul 2003, etc., or is it a once-ff?

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "Bill Foley" <[email protected]> wrote in message
    news:[email protected]...
    > Hey gang,
    >
    > I was wondering if anyone had a solution for this EXCEL query:
    >
    > I have several worksheets in a workbook that has dates of completion of
    > various training topics. Some of these topics require annual (12 months),
    > bi-annual (24 month), 6-month, or even 2-month re-training.
    >
    > What I am trying to accomplish is to automatically shade all cells for

    dates
    > that fall within one month of these periodicities based on the computer's
    > clock when a workbook is opened. Unfortunately different columns have
    > different periodicities so what I was planning on doing was putting that
    > monthly number in a cell ("12" in A1000, for example for all Column A
    > values). When the file is opened, if any of the dates in column A are

    more
    > than 11 months from today's date (A1000 minus 1), they are within one

    month
    > of needing to be re-trained and I want the cell shading to be a light
    > yellow.
    >
    > Can this be done using Conditional Formatting without VBA or do I need
    > something to be run based on an AutoOpen macro?
    >
    > TIA!
    >
    > --
    > Bill Foley
    > www.pttinc.com
    > Microsoft PowerPoint MVP
    >
    >




  4. #4
    Bill Foley
    Guest

    Re: Conditional Formatting Using If Statement On Opening Of Workbook

    Thanks, bpeltzer! I was hoping to figure out exactly one month from that
    day, but this works just fine. Close enough for government work! HA!

    I appreciate it!

    --
    Bill Foley
    www.pttinc.com
    Microsoft PowerPoint MVP
    "bpeltzer" <[email protected]> wrote in message
    news:[email protected]...
    > You should be able to do that with conditional formatting. Set the

    condition
    > to read 'cell value is less than' and the value to compare to is
    > =TODAY()-$A$1000, with the value in $A$1000 set to the number of days

    allowed
    > between retraining less the number of days advance notice desired. You

    could
    > set it up so that you've got multiple conditions with different periods to
    > create shades showing greater urgency, but be sure that you test for the
    > 'most urgent' first.
    >
    > "Bill Foley" wrote:
    >
    > > Hey gang,
    > >
    > > I was wondering if anyone had a solution for this EXCEL query:
    > >
    > > I have several worksheets in a workbook that has dates of completion of
    > > various training topics. Some of these topics require annual (12

    months),
    > > bi-annual (24 month), 6-month, or even 2-month re-training.
    > >
    > > What I am trying to accomplish is to automatically shade all cells for

    dates
    > > that fall within one month of these periodicities based on the

    computer's
    > > clock when a workbook is opened. Unfortunately different columns have
    > > different periodicities so what I was planning on doing was putting that
    > > monthly number in a cell ("12" in A1000, for example for all Column A
    > > values). When the file is opened, if any of the dates in column A are

    more
    > > than 11 months from today's date (A1000 minus 1), they are within one

    month
    > > of needing to be re-trained and I want the cell shading to be a light
    > > yellow.
    > >
    > > Can this be done using Conditional Formatting without VBA or do I need
    > > something to be run based on an AutoOpen macro?
    > >
    > > TIA!
    > >
    > > --
    > > Bill Foley
    > > www.pttinc.com
    > > Microsoft PowerPoint MVP
    > >
    > >
    > >




  5. #5
    Bill Foley
    Guest

    Re: Conditional Formatting Using If Statement On Opening Of Workbook

    Actually bpeltzer sort of gave me a solution, but let me explain further in
    case of another way.

    Let's say A15 has a date of 10/1/2004 (Oct 1, 2004). I need it to be shaded
    when it is within 1 month of 10/1/2005 (Oct 1, 2005). Since it is already
    past that date, it still needs to be shaded until a new date is entered when
    re-training is done. Since there any number of rows and dates, I need it to
    look at each cell in the range and shade it if it is older than 11 months
    from today's date.

    The kicker (that I have not addressed from the previous solution) is I also
    have several cells in a range from A1 - A500 that do not have dates in them
    since these folks did not ever get trained on that topic. When I run the
    "=TODAY()-$A$1000" condition, blank cells are also shaded. I know how to do
    a conditional formula to only run a formula if a cell has a value in it, but
    wasn't sure if that could be done with conditional formatting also.

    THANKS!

    --
    Bill Foley
    www.pttinc.com
    Microsoft PowerPoint MVP
    "Bob Phillips" <[email protected]> wrote in message
    news:%23HqY%[email protected]...
    > Bill,
    >
    > You should be able to do it without VBA .
    >
    > When you say periodicity, will you enter a date like 1st Aug 2000, and

    need
    > it coloured in Jul 2001, Jul 2002, Jul 2003, etc., or is it a once-ff?
    >
    > --
    >
    > HTH
    >
    > RP
    > (remove nothere from the email address if mailing direct)
    >
    >
    > "Bill Foley" <[email protected]> wrote in message
    > news:[email protected]...
    > > Hey gang,
    > >
    > > I was wondering if anyone had a solution for this EXCEL query:
    > >
    > > I have several worksheets in a workbook that has dates of completion of
    > > various training topics. Some of these topics require annual (12

    months),
    > > bi-annual (24 month), 6-month, or even 2-month re-training.
    > >
    > > What I am trying to accomplish is to automatically shade all cells for

    > dates
    > > that fall within one month of these periodicities based on the

    computer's
    > > clock when a workbook is opened. Unfortunately different columns have
    > > different periodicities so what I was planning on doing was putting that
    > > monthly number in a cell ("12" in A1000, for example for all Column A
    > > values). When the file is opened, if any of the dates in column A are

    > more
    > > than 11 months from today's date (A1000 minus 1), they are within one

    > month
    > > of needing to be re-trained and I want the cell shading to be a light
    > > yellow.
    > >
    > > Can this be done using Conditional Formatting without VBA or do I need
    > > something to be run based on an AutoOpen macro?
    > >
    > > TIA!
    > >
    > > --
    > > Bill Foley
    > > www.pttinc.com
    > > Microsoft PowerPoint MVP
    > >
    > >

    >
    >




  6. #6
    Bob Phillips
    Guest

    Re: Conditional Formatting Using If Statement On Opening Of Workbook


    "Bill Foley" <[email protected]> wrote in message
    news:[email protected]...
    >
    > Let's say A15 has a date of 10/1/2004 (Oct 1, 2004). I need it to be

    shaded
    > when it is within 1 month of 10/1/2005 (Oct 1, 2005). Since it is already
    > past that date, it still needs to be shaded until a new date is entered

    when
    > re-training is done. Since there any number of rows and dates, I need it

    to
    > look at each cell in the range and shade it if it is older than 11 months
    > from today's date.


    My reading of the periodicity made me wonder if you would put a date in, and
    then you wanted it highlighted every said period after that date. The fact
    that you are want it to saty shaded until it expires and will then update
    the date makes it significantly easier.

    You can use the technique given, but still use a number of months as you
    originally asked with a formula of

    =DATE(YEAR(TODAY()),MONTH(TODAY())-A$1000+1,DAY(TODAY()))

    > "=TODAY()-$A$1000" condition, blank cells are also shaded. I know how to

    do
    > a conditional formula to only run a formula if a cell has a value in it,

    but
    > wasn't sure if that could be done with conditional formatting also.


    This can be handled, albeit with a more complex formula. To do this, and
    apply to all cells and all sheets

    On the first sheet, select all the cells that will have dates on one
    worksheet (assuming starting at A2)
    Group from that sheet to the last (hold shift and click the final sheet tab)
    Format>Conditional Formatting
    Change conditions 1 to Formula Is
    Add this formula
    =AND(A2<>"",A2<DATE(YEAR(TODAY()),MONTH(TODAY())-A$10+1,DAY(TODAY())))
    Add the format
    OK



  7. #7
    Bill Foley
    Guest

    Re: Conditional Formatting Using If Statement On Opening Of Workbook

    Sure enough worked as well. THANKS!

    One last thing - Any idea how to skip blank cells in a range (or have an IF
    statement that only does the conditional formatting if the cell has a date
    in it)?

    --
    Bill Foley
    www.pttinc.com
    Microsoft PowerPoint MVP
    "Bob Phillips" <[email protected]> wrote in message
    news:[email protected]...
    >
    > "Bill Foley" <[email protected]> wrote in message
    > news:[email protected]...
    > >
    > > Let's say A15 has a date of 10/1/2004 (Oct 1, 2004). I need it to be

    > shaded
    > > when it is within 1 month of 10/1/2005 (Oct 1, 2005). Since it is

    already
    > > past that date, it still needs to be shaded until a new date is entered

    > when
    > > re-training is done. Since there any number of rows and dates, I need

    it
    > to
    > > look at each cell in the range and shade it if it is older than 11

    months
    > > from today's date.

    >
    > My reading of the periodicity made me wonder if you would put a date in,

    and
    > then you wanted it highlighted every said period after that date. The fact
    > that you are want it to saty shaded until it expires and will then update
    > the date makes it significantly easier.
    >
    > You can use the technique given, but still use a number of months as you
    > originally asked with a formula of
    >
    > =DATE(YEAR(TODAY()),MONTH(TODAY())-A$1000+1,DAY(TODAY()))
    >
    > > "=TODAY()-$A$1000" condition, blank cells are also shaded. I know how

    to
    > do
    > > a conditional formula to only run a formula if a cell has a value in it,

    > but
    > > wasn't sure if that could be done with conditional formatting also.

    >
    > This can be handled, albeit with a more complex formula. To do this, and
    > apply to all cells and all sheets
    >
    > On the first sheet, select all the cells that will have dates on one
    > worksheet (assuming starting at A2)
    > Group from that sheet to the last (hold shift and click the final sheet

    tab)
    > Format>Conditional Formatting
    > Change conditions 1 to Formula Is
    > Add this formula
    > =AND(A2<>"",A2<DATE(YEAR(TODAY()),MONTH(TODAY())-A$10+1,DAY(TODAY())))
    > Add the format
    > OK
    >
    >




  8. #8
    Roger Govier
    Guest

    Re: Conditional Formatting Using If Statement On Opening Of Workbook

    Hi Bill

    You could use an AND() statement wrapped around Bob's solution.
    =AND(NOT(ISBLANK(A$1000)),DATE(YEAR(TODAY()),MONTH(TODAY())-A$1000+1,DAY(TODAY())))

    Regards

    Roger Govier


    Bill Foley wrote:
    > Sure enough worked as well. THANKS!
    >
    > One last thing - Any idea how to skip blank cells in a range (or have an IF
    > statement that only does the conditional formatting if the cell has a date
    > in it)?
    >


  9. #9
    Bob Phillips
    Guest

    Re: Conditional Formatting Using If Statement On Opening Of Workbook

    Bill,

    That last formula I gave you, with the way to implement it, should do just
    that.

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "Bill Foley" <[email protected]> wrote in message
    news:[email protected]...
    > Sure enough worked as well. THANKS!
    >
    > One last thing - Any idea how to skip blank cells in a range (or have an

    IF
    > statement that only does the conditional formatting if the cell has a date
    > in it)?
    >
    > --
    > Bill Foley
    > www.pttinc.com
    > Microsoft PowerPoint MVP
    > "Bob Phillips" <[email protected]> wrote in message
    > news:[email protected]...
    > >
    > > "Bill Foley" <[email protected]> wrote in message
    > > news:[email protected]...
    > > >
    > > > Let's say A15 has a date of 10/1/2004 (Oct 1, 2004). I need it to be

    > > shaded
    > > > when it is within 1 month of 10/1/2005 (Oct 1, 2005). Since it is

    > already
    > > > past that date, it still needs to be shaded until a new date is

    entered
    > > when
    > > > re-training is done. Since there any number of rows and dates, I need

    > it
    > > to
    > > > look at each cell in the range and shade it if it is older than 11

    > months
    > > > from today's date.

    > >
    > > My reading of the periodicity made me wonder if you would put a date in,

    > and
    > > then you wanted it highlighted every said period after that date. The

    fact
    > > that you are want it to saty shaded until it expires and will then

    update
    > > the date makes it significantly easier.
    > >
    > > You can use the technique given, but still use a number of months as you
    > > originally asked with a formula of
    > >
    > > =DATE(YEAR(TODAY()),MONTH(TODAY())-A$1000+1,DAY(TODAY()))
    > >
    > > > "=TODAY()-$A$1000" condition, blank cells are also shaded. I know how

    > to
    > > do
    > > > a conditional formula to only run a formula if a cell has a value in

    it,
    > > but
    > > > wasn't sure if that could be done with conditional formatting also.

    > >
    > > This can be handled, albeit with a more complex formula. To do this, and
    > > apply to all cells and all sheets
    > >
    > > On the first sheet, select all the cells that will have dates on one
    > > worksheet (assuming starting at A2)
    > > Group from that sheet to the last (hold shift and click the final sheet

    > tab)
    > > Format>Conditional Formatting
    > > Change conditions 1 to Formula Is
    > > Add this formula
    > > =AND(A2<>"",A2<DATE(YEAR(TODAY()),MONTH(TODAY())-A$10+1,DAY(TODAY())))
    > > Add the format
    > > OK
    > >
    > >

    >
    >




  10. #10
    Bill Foley
    Guest

    Re: Conditional Formatting Using If Statement On Opening Of Workbook

    I tried it but all the blank cells in the range I applied the conditional
    formatting got shaded as well. I will continue to look into it.

    THANKS!

    --
    Bill Foley
    www.pttinc.com
    Microsoft PowerPoint MVP
    "Bob Phillips" <[email protected]> wrote in message
    news:[email protected]...
    > Bill,
    >
    > That last formula I gave you, with the way to implement it, should do just
    > that.
    >
    > --
    >
    > HTH
    >
    > RP
    > (remove nothere from the email address if mailing direct)
    >
    >
    > "Bill Foley" <[email protected]> wrote in message
    > news:[email protected]...
    > > Sure enough worked as well. THANKS!
    > >
    > > One last thing - Any idea how to skip blank cells in a range (or have an

    > IF
    > > statement that only does the conditional formatting if the cell has a

    date
    > > in it)?
    > >
    > > --
    > > Bill Foley
    > > www.pttinc.com
    > > Microsoft PowerPoint MVP
    > > "Bob Phillips" <[email protected]> wrote in message
    > > news:[email protected]...
    > > >
    > > > "Bill Foley" <[email protected]> wrote in message
    > > > news:[email protected]...
    > > > >
    > > > > Let's say A15 has a date of 10/1/2004 (Oct 1, 2004). I need it to

    be
    > > > shaded
    > > > > when it is within 1 month of 10/1/2005 (Oct 1, 2005). Since it is

    > > already
    > > > > past that date, it still needs to be shaded until a new date is

    > entered
    > > > when
    > > > > re-training is done. Since there any number of rows and dates, I

    need
    > > it
    > > > to
    > > > > look at each cell in the range and shade it if it is older than 11

    > > months
    > > > > from today's date.
    > > >
    > > > My reading of the periodicity made me wonder if you would put a date

    in,
    > > and
    > > > then you wanted it highlighted every said period after that date. The

    > fact
    > > > that you are want it to saty shaded until it expires and will then

    > update
    > > > the date makes it significantly easier.
    > > >
    > > > You can use the technique given, but still use a number of months as

    you
    > > > originally asked with a formula of
    > > >
    > > > =DATE(YEAR(TODAY()),MONTH(TODAY())-A$1000+1,DAY(TODAY()))
    > > >
    > > > > "=TODAY()-$A$1000" condition, blank cells are also shaded. I know

    how
    > > to
    > > > do
    > > > > a conditional formula to only run a formula if a cell has a value in

    > it,
    > > > but
    > > > > wasn't sure if that could be done with conditional formatting also.
    > > >
    > > > This can be handled, albeit with a more complex formula. To do this,

    and
    > > > apply to all cells and all sheets
    > > >
    > > > On the first sheet, select all the cells that will have dates on one
    > > > worksheet (assuming starting at A2)
    > > > Group from that sheet to the last (hold shift and click the final

    sheet
    > > tab)
    > > > Format>Conditional Formatting
    > > > Change conditions 1 to Formula Is
    > > > Add this formula
    > > > =AND(A2<>"",A2<DATE(YEAR(TODAY()),MONTH(TODAY())-A$10+1,DAY(TODAY())))
    > > > Add the format
    > > > OK
    > > >
    > > >

    > >
    > >

    >
    >




  11. #11
    Bob Phillips
    Guest

    Re: Conditional Formatting Using If Statement On Opening Of Workbook

    Bill,

    Do you want to post me the workbook?

    Bob

    "Bill Foley" <[email protected]> wrote in message
    news:[email protected]...
    > I tried it but all the blank cells in the range I applied the conditional
    > formatting got shaded as well. I will continue to look into it.
    >
    > THANKS!
    >
    > --
    > Bill Foley
    > www.pttinc.com
    > Microsoft PowerPoint MVP
    > "Bob Phillips" <[email protected]> wrote in message
    > news:[email protected]...
    > > Bill,
    > >
    > > That last formula I gave you, with the way to implement it, should do

    just
    > > that.
    > >
    > > --
    > >
    > > HTH
    > >
    > > RP
    > > (remove nothere from the email address if mailing direct)
    > >
    > >
    > > "Bill Foley" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > Sure enough worked as well. THANKS!
    > > >
    > > > One last thing - Any idea how to skip blank cells in a range (or have

    an
    > > IF
    > > > statement that only does the conditional formatting if the cell has a

    > date
    > > > in it)?
    > > >
    > > > --
    > > > Bill Foley
    > > > www.pttinc.com
    > > > Microsoft PowerPoint MVP
    > > > "Bob Phillips" <[email protected]> wrote in message
    > > > news:[email protected]...
    > > > >
    > > > > "Bill Foley" <[email protected]> wrote in message
    > > > > news:[email protected]...
    > > > > >
    > > > > > Let's say A15 has a date of 10/1/2004 (Oct 1, 2004). I need it to

    > be
    > > > > shaded
    > > > > > when it is within 1 month of 10/1/2005 (Oct 1, 2005). Since it is
    > > > already
    > > > > > past that date, it still needs to be shaded until a new date is

    > > entered
    > > > > when
    > > > > > re-training is done. Since there any number of rows and dates, I

    > need
    > > > it
    > > > > to
    > > > > > look at each cell in the range and shade it if it is older than 11
    > > > months
    > > > > > from today's date.
    > > > >
    > > > > My reading of the periodicity made me wonder if you would put a date

    > in,
    > > > and
    > > > > then you wanted it highlighted every said period after that date.

    The
    > > fact
    > > > > that you are want it to saty shaded until it expires and will then

    > > update
    > > > > the date makes it significantly easier.
    > > > >
    > > > > You can use the technique given, but still use a number of months as

    > you
    > > > > originally asked with a formula of
    > > > >
    > > > > =DATE(YEAR(TODAY()),MONTH(TODAY())-A$1000+1,DAY(TODAY()))
    > > > >
    > > > > > "=TODAY()-$A$1000" condition, blank cells are also shaded. I know

    > how
    > > > to
    > > > > do
    > > > > > a conditional formula to only run a formula if a cell has a value

    in
    > > it,
    > > > > but
    > > > > > wasn't sure if that could be done with conditional formatting

    also.
    > > > >
    > > > > This can be handled, albeit with a more complex formula. To do this,

    > and
    > > > > apply to all cells and all sheets
    > > > >
    > > > > On the first sheet, select all the cells that will have dates on one
    > > > > worksheet (assuming starting at A2)
    > > > > Group from that sheet to the last (hold shift and click the final

    > sheet
    > > > tab)
    > > > > Format>Conditional Formatting
    > > > > Change conditions 1 to Formula Is
    > > > > Add this formula
    > > > >

    =AND(A2<>"",A2<DATE(YEAR(TODAY()),MONTH(TODAY())-A$10+1,DAY(TODAY())))
    > > > > Add the format
    > > > > OK
    > > > >
    > > > >
    > > >
    > > >

    > >
    > >

    >
    >




  12. #12
    Bill Foley
    Guest

    Re: Conditional Formatting Using If Statement On Opening Of Workbook

    If you want, you can send anything you have directly to me at:

    pttinc at itexas dot net

    THANKS!

    --
    Bill Foley
    Microsoft PowerPoint MVP
    www.pttinc.com
    "Bob Phillips" <[email protected]> wrote in message
    news:[email protected]...
    > Bill,
    >
    > Do you want to post me the workbook?
    >
    > Bob
    >
    > "Bill Foley" <[email protected]> wrote in message
    > news:[email protected]...
    >> I tried it but all the blank cells in the range I applied the conditional
    >> formatting got shaded as well. I will continue to look into it.
    >>
    >> THANKS!
    >>
    >> --
    >> Bill Foley
    >> www.pttinc.com
    >> Microsoft PowerPoint MVP
    >> "Bob Phillips" <[email protected]> wrote in message
    >> news:[email protected]...
    >> > Bill,
    >> >
    >> > That last formula I gave you, with the way to implement it, should do

    > just
    >> > that.
    >> >
    >> > --
    >> >
    >> > HTH
    >> >
    >> > RP
    >> > (remove nothere from the email address if mailing direct)
    >> >
    >> >
    >> > "Bill Foley" <[email protected]> wrote in message
    >> > news:[email protected]...
    >> > > Sure enough worked as well. THANKS!
    >> > >
    >> > > One last thing - Any idea how to skip blank cells in a range (or have

    > an
    >> > IF
    >> > > statement that only does the conditional formatting if the cell has a

    >> date
    >> > > in it)?
    >> > >
    >> > > --
    >> > > Bill Foley
    >> > > www.pttinc.com
    >> > > Microsoft PowerPoint MVP
    >> > > "Bob Phillips" <[email protected]> wrote in message
    >> > > news:[email protected]...
    >> > > >
    >> > > > "Bill Foley" <[email protected]> wrote in message
    >> > > > news:[email protected]...
    >> > > > >
    >> > > > > Let's say A15 has a date of 10/1/2004 (Oct 1, 2004). I need it
    >> > > > > to

    >> be
    >> > > > shaded
    >> > > > > when it is within 1 month of 10/1/2005 (Oct 1, 2005). Since it
    >> > > > > is
    >> > > already
    >> > > > > past that date, it still needs to be shaded until a new date is
    >> > entered
    >> > > > when
    >> > > > > re-training is done. Since there any number of rows and dates, I

    >> need
    >> > > it
    >> > > > to
    >> > > > > look at each cell in the range and shade it if it is older than
    >> > > > > 11
    >> > > months
    >> > > > > from today's date.
    >> > > >
    >> > > > My reading of the periodicity made me wonder if you would put a
    >> > > > date

    >> in,
    >> > > and
    >> > > > then you wanted it highlighted every said period after that date.

    > The
    >> > fact
    >> > > > that you are want it to saty shaded until it expires and will then
    >> > update
    >> > > > the date makes it significantly easier.
    >> > > >
    >> > > > You can use the technique given, but still use a number of months
    >> > > > as

    >> you
    >> > > > originally asked with a formula of
    >> > > >
    >> > > > =DATE(YEAR(TODAY()),MONTH(TODAY())-A$1000+1,DAY(TODAY()))
    >> > > >
    >> > > > > "=TODAY()-$A$1000" condition, blank cells are also shaded. I
    >> > > > > know

    >> how
    >> > > to
    >> > > > do
    >> > > > > a conditional formula to only run a formula if a cell has a value

    > in
    >> > it,
    >> > > > but
    >> > > > > wasn't sure if that could be done with conditional formatting

    > also.
    >> > > >
    >> > > > This can be handled, albeit with a more complex formula. To do
    >> > > > this,

    >> and
    >> > > > apply to all cells and all sheets
    >> > > >
    >> > > > On the first sheet, select all the cells that will have dates on
    >> > > > one
    >> > > > worksheet (assuming starting at A2)
    >> > > > Group from that sheet to the last (hold shift and click the final

    >> sheet
    >> > > tab)
    >> > > > Format>Conditional Formatting
    >> > > > Change conditions 1 to Formula Is
    >> > > > Add this formula
    >> > > >

    > =AND(A2<>"",A2<DATE(YEAR(TODAY()),MONTH(TODAY())-A$10+1,DAY(TODAY())))
    >> > > > Add the format
    >> > > > OK
    >> > > >
    >> > > >
    >> > >
    >> > >
    >> >
    >> >

    >>
    >>

    >
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1