macro to set the date and count down.......Anyone?
macro to set the date and count down.......Anyone?
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

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

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    Manchester UK
    Posts
    133
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Ahmedabad Gujarat
    Posts
    303
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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

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
  •  

 

 
DMCA.com