1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel 2007 Date Variable

Discussion in 'Business Applications' started by mariaa33, Dec 17, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. mariaa33

    mariaa33 Thread Starter

    Joined:
    Aug 7, 2008
    Messages:
    164
    I have a macro that I want the user to input a date such as 12/08/08 and then conditional format column O for any cells containing date greater than the user's input date. The only thing it highlights is the header. I am not sure how to get the formula= MyDate to function correctly. Any help would be appreciated.

    Code:
    Sub TestUserInput()
    '
    ' Macro1 Macro
    '
    '
    Dim MyDate As Date
    Columns("O:O").Select
    MyDate = Application.InputBox("Enter a Date")
    ' MyDate = DateValue(MyDate)
    MsgBox "The name you entered was " & MyDate
     
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual _
    , Formula1:="& MyDate"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .Color = 65535
    .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    End Sub
    
     
  2. Sponsor

  3. computerman29642

    computerman29642

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    You could always do something like this.

    Code:
    Dim MyDate As Date
    Dim rng As Range
    
    MyDate = Application.InputBox("Enter a Date")
    MsgBox "User entered the following date: " & MyDate
    
    Set rng = Range("O:O") 'or Set rng = Range("O1:O25") 
    
    For Each d In rng
        If d > MyDate Then
                d.Interior.PatternColorIndex = xlAutomatic
                d.Interior.Color = 65535
                d.Interior.TintAndShade = 0
        End If
    Next
    
     
  4. mariaa33

    mariaa33 Thread Starter

    Joined:
    Aug 7, 2008
    Messages:
    164
    Works great. Thank you.
     
  5. computerman29642

    computerman29642

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    You are very welcome. :)
     
  6. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Since you're having user input (which should always spell T R O U B L E), and using VBA, you should make use of the built-in function IsDate()...

    Code:
    'right after this line...
    MyDate = Application.InputBox("Enter a Date")
    
    '.. put this line...
    If IsDate(MyDate) = False Then Exit Sub
    HTH
     
  7. computerman29642

    computerman29642

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    Thanks Zack. :)
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/780562