macro to set the date and count down.......Anyone?

[jamienwood]

I would like a macro that can apply todays date to A1 and i want it to be fixed so when you log on the next day it doesnt change, i also want a date in A2 that constantly changes, day by day, and the in cell A3 a count down of 30 days from the date in A1 and if possible the cell to do a conditional format so it changes to red when the page is 30 days old. Can it be done, and all in a macro???

[nisht]

First of all you need to format cell a1 and cell a2 to date format and cell a3 to number format.


Sub countdays()

If Isempty([a1]) Then
[a1] = VBA.Date
[a2] = VBA.Date
Else
[a2] = VBA.Date
End If

[a3] = [a2] - [a1]

End Sub


i hope this will solve your problem

nishith desai
http://www.pexcel.com


[ This Message was edited by: nisht on 2002-04-16 07:31 ]

[ This Message was edited by: nisht on 2002-04-16 07:34 ]

[ This Message was edited by: nisht on 2002-04-19 03:04 ]

[Tom Schreiner]

Hi
Try this out.
You will need to change sheet1 to your sheet's name...

Private Sub Workbook_Open()
If Sheet1.Range("A1").Value = 0 Then _
Sheet1.Range("A1").Value = Format(Now, "MM/DD/YY")
Sheet1.Range("A2").Value = Format(Now, "MM/DD/YY")
Sheet1.Range("A3").Value = 30 - DateDiff("d", _
Sheet1.Range("A1").Value, Sheet1.Range("A2").Value)
If Sheet1.Range("A3").Value <= 0 Then
MsgBox "Sheet1 is " & DateDiff("d", _
Sheet1.Range("A1").Value, Sheet1.Range("A2").Value) & _
" days old."
With Range("A3").Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
End If
End Sub

Tom