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?
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.
- This worked, thank you so much!!
