Forum Discussion
Adding auto-change date cell with data cell
In this spreadsheet I am wanting to have cell 8J update to the current date anytime the data in cell 8C is changed. Is there a simple formula for this without doing VBA codes?
4 Replies
If you want to enter the current date (or date and time) in J8 the first time C8 is filled, you can use a formula.
If you want J8 to change each time C8 is changed, you'd need VBA code.
- Thanks for the quick response. I found another thread discussing something similar and tried to copy the VBA code that was given in that thread & substitute my desired cells but it did not work for me. If you have suggestions on a VBA code that would work for this function I'm trying to achieve that would be much appreciated and I will give that another try.
Right-click the sheet tab.
Select 'View Code' from the context menu.
Copy the following code into the worksheet module:
Private Sub Worksheet_Change(ByVal Target As Range) ' Add or remove cells as needed, separated by commas, for example "C8,C15,C22" Const Cells2Watch = "C8" Dim Rng As Range If Not Intersect(Range(Cells2Watch), Target) Is Nothing Then Application.ScreenUpdating = False Application.EnableEvents = False For Each Rng In Intersect(Range(Cells2Watch), Target) If Rng.Value = "" Then Range("J" & Rng.Row).ClearContents Else Range("J" & Rng.Row).Value = Date End If Next Rng Application.EnableEvents = True Application.ScreenUpdating = True End If End SubSwitch back to Excel.
Save the workbook as a macro-enabled workbook (*.xlsm).
Make sure that you allow macros when you open the workbook.
