Announcement

Collapse
No announcement yet.

VBA Date Check

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • VBA Date Check



    Hello,

    I am new to using VBA but have some coding in my background.

    I am trying to use this code below so that as I change a cell the date changes to the current change and this works well.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    'Update 20140722
    Dim WorkRng As Range
    Dim Rng As Range
    Dim xOffsetColumn As Integer
    Set WorkRng = Intersect(Application.ActiveSheet.Range("I:I"), Target)
    xOffsetColumn = 7
    If Not WorkRng Is Nothing Then
    Application.EnableEvents = False
    For Each Rng In WorkRng
    If Not VBA.IsEmpty(Rng.Value) Then
    Rng.Offset(0, xOffsetColumn).Value = Now
    Rng.Offset(0, xOffsetColumn).NumberFormat = "mm/dd/yyyy"
    Else
    Rng.Offset(0, xOffsetColumn).ClearContents
    End If
    Next
    Application.EnableEvents = True
    End If
    End Sub


    However, I don't want this date entry to populate when I create new entries. What is the best was to modify this code to do that? Here is some background on the issue:
    In Column A it has the date the entry was added. This has 2 different entries: The actual date it was entered, and "Original" if it was there prior. Is there a way to only run this if the value in the corresponding row in Column A is < Current date or original?

    I tried to play around with this but since it's running as a range I don't have the background to understand the proper way to fix this.

    Any help would be much appreciated! Thank you!
    Last edited by AlanSidman; 1 week ago.

  • #2
    Hello and Welcome to the Forum

    Not totally sure to understand your objective ...

    May be following macro can help

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    ' Update 20190911
    ' User inputs data in Column I
    ' With Offset equal to 7, Column P will show Today's date
    ' as long as the Date in Column A is smaller than Today's date
    
    If Target.Column <> 9 Then Exit Sub
    Dim xOffsetColumn As Integer
    Application.EnableEvents = False
    xOffsetColumn = 7
        
        If Target.Offset(0, -8) < Date Then
            If Not VBA.IsEmpty(Target) Then
                Target.Offset(0, xOffsetColumn).Value = Date
                Target.Offset(0, xOffsetColumn).NumberFormat = "mm/dd/yyyy"
            Else
                Target.Offset(0, xOffsetColumn).ClearContents
            End If
         End If
    Application.EnableEvents = True
    End Sub
    If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

    Comment


    • #3
      Hello and Thank You!

      This works great for all the dates that are less than the current date. Thank You!

      However, there is one more objective.
      - In many rows in column A (the date entered column), the column A entry is "Original". This is referencing data that was never added and has existed since the file was made.

      Is there any way for rows with the entry "Original" in column A to also be affected and update with the current date if altered.

      Thank you again for your help!

      Comment


      • #4
        Code Tags Added
        Your post does not comply with our Forum RULES. Use code tags around code.

        Posting code between tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

        Highlight your code and click the # icon at the top of your post window.

        (I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)
        Alan

        Did you debug your code first? http://www.cpearson.com/excel/DebuggingVBA.aspx
        FORUM RULES----->http://www.ozgrid.com/forum/announcement.php?f=8

        If someone has helped you, say "thank you" by clicking on the Like Button.

        Comment


        • #5
          Glad it is helping you out ...

          Below is the latest modification

          Code:
           Private Sub Worksheet_Change(ByVal Target As Range)
          ' Update 20190911
          ' User inputs data in Column I
          ' With Offset equal to 7, Column P will show Today's date
          ' as long as the Date in Column A is smaller than Today's date  
          If Target.Column <> 9 Then Exit Sub
          Dim xOffsetColumn As Integer
          Application.EnableEvents = False
          xOffsetColumn = 7
                   If Target.Offset(0, -8) < Date Or Target.Offset(0,-8) = "Original" Then
                          If Not VBA.IsEmpty(Target) Then
                             Target.Offset(0, xOffsetColumn).Value = Date
                             Target.Offset(0, xOffsetColumn).NumberFormat = "mm/dd/yyyy"
                           Else
                             Target.Offset(0, xOffsetColumn).ClearContents
                          End If
                   End If
          Application.EnableEvents = True
          End Sub
          Hope this will help
          Last edited by Carim; 1 week ago.
          If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

          Comment


          • #6
            Perfect Thank You so much!

            Comment


            • #7


              Originally posted by duxromuma View Post
              Perfect Thank You so much!
              Glad it is all sorted out ...

              Thanks a lot for ... your Thanks AND for the Like ...
              If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

              Comment

              Working...
              X