auto date changes

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?

[Derek]

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

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.

[Derek]

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

[Derek]

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 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

[Derek]

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

Thanks Derek, what you suggested worked!
Jon