+ Reply to Thread
Results 1 to 5 of 5

VBA code help for item tracking.

  1. #1

    VBA code help for item tracking.

    Hello,
    I'm asking for vba code (or resources on the web) that will help with
    the following:

    When a date is entered in cell a1, I would like excel to calculate if
    that date was 120 days ago from TODAY(). If it wasn't, leave the cell
    format alone. If it was, change the background color (to notify me the
    item is overdue) until a date is entered in b1. When a date is entered
    in b1, remove background color from a1 (to notify me that the item has
    been checked-in).

    Thanks for any help,
    Russ


  2. #2
    Myrna Larson
    Guest

    Re: VBA code help for item tracking.

    I don't think you need VBA for this. Conditional Formatting should take care
    of it. Check it out in Help. Select A1, then Format/Conditional Formatting,
    Format Is

    =IF(AND(B1="",TODAY()-A1>120))

    Then select the format you want for overdue items.


    On 23 Mar 2005 17:57:24 -0800, [email protected] wrote:

    >Hello,
    >I'm asking for vba code (or resources on the web) that will help with
    >the following:
    >
    >When a date is entered in cell a1, I would like excel to calculate if
    >that date was 120 days ago from TODAY(). If it wasn't, leave the cell
    >format alone. If it was, change the background color (to notify me the
    >item is overdue) until a date is entered in b1. When a date is entered
    >in b1, remove background color from a1 (to notify me that the item has
    >been checked-in).
    >
    >Thanks for any help,
    >Russ



  3. #3
    David
    Guest

    Re: VBA code help for item tracking.

    Myrna Larson wrote

    > =IF(AND(B1="",TODAY()-A1>120))


    Results in 'Your formula contains an error' i.e. needs something between
    last 2 ))

    --
    David

  4. #4
    Mike Fogleman
    Guest

    Re: VBA code help for item tracking.

    Just use the AND function
    AND(B1="",TODAY()-A1>120)

    Mike F

    "David" <[email protected]> wrote in message
    news:%[email protected]...
    > Myrna Larson wrote
    >
    >> =IF(AND(B1="",TODAY()-A1>120))

    >
    > Results in 'Your formula contains an error' i.e. needs something between
    > last 2 ))
    >
    > --
    > David




  5. #5

    Re: VBA code help for item tracking.

    Thanks for all of the responses! I'm wondering if anyone is seeing the
    same thing I am: When I apply that formula to a blank cell, the cell
    changes to the formatting I selected without me entering a date. Is
    there a default date value in each cell that is evaluated in the
    formula that would cause this change? The formula works beautifully if
    there is a date already in the cell.

    Many thanks again,
    Russ


+ 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