Announcement

Collapse
No announcement yet.

Automatically add date when cells change

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

  • Automatically add date when cells change



    I want to add the date to a rows cell when ever a cell in the same row has been modified. I used the following code but it only updates the date when a cell in Column D is changed. I want the date in Column E to change when either Columns C, D, F - S are modified.
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    'add date when status changes
    Dim WorkRng As Range
    Dim Rng As Range
    Dim xOffsetColumn As Integer
    Set WorkRng = Intersect(Application.ActiveSheet.Range("D:D"), Target)
    xOffsetColumn = 1
    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 = "dd/mm/yyyy"
    Else
    Rng.Offset(0, xOffsetColumn).ClearContents
    End If
    Next
    Application.EnableEvents = True
    End If
    End Sub
    Thank you

  • #2
    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


    • #3
      Hello,

      You could test following event macro :

      Code:
      Private Sub Worksheet_Change(ByVal Target As Range)
      ' Adds Date when Status changes in Columns C,D,F and S
      If Target.Count > 1 Then Exit Sub
      If Intersect(Target, Range("C:C,D:D,F:F,S:S")) Is Nothing Then Exit Sub
        Application.EnableEvents = False
            If Target.Value <> vbNullString Then
              Target.Offset(0, 5 - Target.Column).Value = Now
              Target.Offset(0, 5 - Target.Column).NumberFormat = "dd/mm/yyyy"
            Else
              Target.Offset(0, 5 - Target.Column).ClearContents
            End If
        Application.EnableEvents = True
      End Sub
      Hope this will help
      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


      • #4
        Thanks a bunch. It worked perfectly.

        Comment


        • #5


          Glad this could help you out ..

          Thanks .. 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