Update cell in date column to a new date based on data in other cells

Posted on 2014-08-11
Last Modified: 2014-08-13
Column G is a date column that show the last time a chapter was updated.

I would like the date in Column G to be automatically updated to a static date (example of format: "15 NOV 14") if the following criteria is met:
If Column I, Column N, Column S, Column X, Column AC OR Column AH = "NEW" or "REVISED"
Otherwise, the pre-existing date in Column G is to remain intact and unchanged.

I've attached a sample spreadsheet.

Question by:Andreamary
    LVL 27

    Expert Comment

    by:Glenn Ray
    The dates in your example workbook are static dates (i.e, constants) right now.  Would these be formulas usually?  If not, what should the new date be converted to?  Today's date?  Another calculated date based on other data?

    A Worksheet_Change event should handle this, but will wait to hear clarification from you.

    LVL 27

    Accepted Solution

    Just as an example, here is a Worksheet_Change event that would change the Last Revision Date value in column G to today's date if any of the above-mentioned columns were to be changed.  
    Option Explicit
    Dim rng As Range
    Dim cl As Object
    Dim boolChange As Boolean
    Dim x As Integer
    Private Sub Worksheet_Change(ByVal Target As Range)
        If boolChange Then
            boolChange = False
            Exit Sub
        End If
        Set rng = Range("G2", Range("G2").End(xlDown))
        For Each cl In rng
            For x = 2 To 27 Step 5
                If cl.Offset(0, x) = "New" Or cl.Offset(0, x) = "Revised" Then
                    boolChange = True
                    cl.Value = Format(Now(), "dd-mmm-yy")
                    Exit For
                End If
            Next x
        Next cl
        boolChange = False
    End Sub

    Open in new window

    This code would be inserted into the "Book_Prod_Sept2014" sheet object in the VBA Project.  I've attached a modified version of your file that shows this.  I had to replicate your Data Validation range (TypeChange1) on another sheet to test.


    Author Closing Comment

    Terrific, Glenn...the only modification I made was to line 16 (as shown below) since we use the same date for all changes within a cycle (and not the actual date the change is done on). So for each cycle, I'll update the code with the new 'static' date to be used.

    cl.Value = Format("15-Nov-14", "dd-mmm-yy")

    Thanks so much!

    LVL 27

    Expert Comment

    by:Glenn Ray
    You're welcome.  

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    This is pretty cool.  The purpose of this VB Script is to help you document where JAR (Java ARchive) files and specifically java class files are located so that you can address issues seen with a client or that you can speak intelligently with a dev…
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now