auto date changes

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

Thanks Derek, what you suggested worked!
Jon