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

jamienwood

Board Regular
Joined
Apr 14, 2002
Messages
133
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???
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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

ni****h 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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top