Formula Help

Copper Contributor

Hi All,

 

I would like a formula which when i Input an update in column F then the date updated column H should be automatically populated with that days date. Is this possible 

 

Thank You

8 Replies

@Harry2145 

This requires VBA.

Right-click the sheet tab.

Select 'View Code' from the context menu.

Copy the code listed below into the worksheet module.

Switch back to Excel.

Save the workbook as a macro-enabled workbook (*.xlsm).

Make sure that you allow macros when you open it.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("F2:F" & Rows.Count), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        Intersect(Range("F2:F" & Rows.Count), Target).Offset(0, 2).Value = Date
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub
@Hans Vogelaar thank you for your reply, will this apply for the full column and will work with any update which will display the date that it was updated
Also if I would like to change the columns where will I edit this

@Harry2145 

It will work for the entire column except for the cell in row 1 - I assumed that F1 would contain a header.

Each time you edit one or more cells in F2:F1048576, the corresponding cell(s) in column H will be set to the then current date.

@Harry2145 

Column F is mentioned explicitly in the code.

The cells that will contain the date are specified as an offset to this column: the .Offset(0, 2) in the code tells Excel to use the cells two columns to the right. If you want the dates in column C, i.e. 4 columns to the left of column F, use .Offset(0, -4)

@Hans Vogelaar this code is perfect thank you but when I delete something from column f the date still remains even though the cell is empty how do I get rid of the date if the cell is empty 

@Harry2145 

This version will clear the date in column H if the cell in column F is cleared.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    If Not Intersect(Range("F2:F" & Rows.Count), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        For Each rng In Intersect(Range("F2:F" & Rows.Count), Target)
            If rng.Value = "" Then
                rng.Offset(0, 2).ClearContents
            Else
                rng.Offset(0, 2).Value = Date
            End If
        Next rng
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub
Thank you very much