Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: auto date changes

  1. #1
    Guest

    Default

    I am using conditional formatting to change a cells color as it gets within 2 days of a due date, thanks to you providing the solution for another user. My question is:
    once the due date arrives, is there a way to make it change automatically to 30 days from that date?

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    I assume that you mean that 2 days before the due date your cell (I have used A4) turns yellow and 30 days after due date cell turns red. If so, put this formula in A1 =today() and use the following conditional formatting on cell A4 containing your due date:

    1. formula is =$A$1>=A4+30 format cell red
    2. cell value is less than or equal to =$A$1-2 format cell yellow

    Hope this helps
    regards
    Derek

  3. #3
    Guest

    Default

    Thanks for replying Derek.

    Actually I want the date to adjust automatically once it reaches the due date.
    For example, today the cell, which has a date of 3/12/02, turned red because it is 2 days before 3/12/02. On 3/12/02 I would like the date in that cell to automatically change to 4/11/02, 30 days away, my next due date.


  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

    You could use an event macro in your worksheet. Again this assumes your due date is in A4 and the formula in A1 is =today()

    Right mouse sheet tab, left mouse View Code, paste this in

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("A1").Value = Range("A4").Value Then Range("A4").Value = Range("A4").Value + 30
    End Sub

    I am not sure how you could do this if you have many cells with due dates.

    HTH
    regards
    Derek

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi again
    Actually if you have several due dates (say in the range A4:A12) you could use this:

    Private Sub Worksheet_Change(ByVal Target As Range)
    For Each cell In Range("A4:A12")
    If cell.Value = Range("A1").Value Then cell.Value = cell.Value + 30
    Next
    End Sub

    regards
    Derek

  6. #6
    Guest

    Default

    Hi Derek,

    In the cell with the date, all I have done is formatted that cell for dates, and used conditional formatting to alert me. I have been manually entering the new due date every 30 days. So I have not been using another cell with a formula. Would your solution still work under those conditions?
    Thanks for the help,
    Jon

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-10 17:04, Derek wrote:
    Hi again
    Actually if you have several due dates (say in the range A4:A12) you could use this:

    Private Sub Worksheet_Change(ByVal Target As Range)
    For Each cell In Range("A4:A12")
    If cell.Value = Range("A1").Value Then cell.Value = cell.Value + 30
    Next
    End Sub

    regards
    Derek

    Hi Jon
    I assume in your conditional formatting you use a formula referring to Today() or Now().
    Just change the above code to:

    Private Sub Worksheet_Change(ByVal Target As Range)
    For Each cell In Range("A4:A12")
    If cell.Value = Date Then cell.Value = cell.Value + 30
    Next
    End Sub

    That should do it (you can test it by changing the due date in your cell to today's date)

    regards
    Derel


  8. #8
    Guest

    Default

    Thanks Derek, what you suggested worked!
    Jon

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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