Creating a cell that returns a date when others are modified

[beats_86]

Gday,
I have a calendar type spreadsheet that has a series of sheets in it, one for each month of the year. At the bottom of each sheet is a cell displaying "Correct as:" and then a date. Is there a way to make the date cell update whenever someone changes data on any of the sheets?

Currently I have the dates update of a master sheet, being january, but I have to update that master date manually.

 

[AlphaFrog]

When you make a change to any sheet that has a cell (any cell) that starts with "Correct As:", the macro below will add or update the date e.g.
Correct as: Apr-06-2011

To install the macro...

• Right-click on the Excel icon in the upper left
• Select View Code from the pop-up menu
• Paste the code below in the VBA edit window
• This code should go in the Thisworkbook module

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    Dim rng As Range
    
    Set rng = Sh.Cells.Find("Correct as:*", , , xlWhole, , , False)
    
    If Not rng Is Nothing Then
        If Intersect(Target, rng) Is Nothing Then
            Application.EnableEvents = False
            rng.Value = "Correct as: " & Format(Date, [COLOR="Red"]"mmm-dd-yyyy"[/COLOR])
            Application.EnableEvents = True
        End If
    End If
    
End Sub

The date format you want to display is in Red.
If the macro can't find a cell on the sheet that starts with "Correct as:" then it does nothing to that sheet.

 

[beats_86]

Thanks for that. Is there any way to make it update the other sheets when one sheet gets updated (eg if I change something in the April sheet, it updates all the other sheets too)?

 

[AlphaFrog]

I would suggest that All the sheets from February to December have their Change As: cells have a formula something like this...
=January!C10

Then the macro below updates only cell C10 on sheet January when a change happens to any sheet.. The formulas on the other sheets will get their dates from January cell C10 (or what ever cell you want).

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Application.EnableEvents = False
Sheets("[COLOR="Red"]January[/COLOR]").Range("[COLOR="Red"]C10[/COLOR]").Value = "Correct as: " & Format(Date, "mmm-dd-yyyy")
Application.EnableEvents = True
    
End Sub

 

[beats_86]

Perfect. Thanks very much.