VBA code for timestamp- cell's containing formulas

I have a workbook with 3 worksheets. Worksheet1 has rolls ups of worksheet2 and worksheet3. So worksheet1 is never manually updated because the data/information is updated in worksheet2/3 and it populates into worksheet1 through a v lookup formula in the cells.

I want a timestamp for whenever the cells in column D in worksheet1 contain a percent of 1%-99%.

I want this timestamp to never change after the first data is entered.

I also want the timestamp to print in column G

I want another timestamp for whenever the cells in column D in worksheet1 contain a 100%.

I want this timestamp to never change after the first data is entered.

I also want the timestamp to print in column H.

Please help, I've tried many different formulas.

this was the last formula i used and it doesn't work because it only picks up when the cells are changed manually. but since the worksheet populates from formulas it didn't catch the change.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myTableRange As Range
Dim myDateTimeRage As Range
Dim myUpdatedRange As Range

Set myTableRange = Range("D1:D314")

If Intersect(Target, myTableRange) Is Nothing Then Exit Sub

Set myDateTimeRage = Range("N" & Target.Row)
Set myUpdatedRange = Range("O" & Target.Row)

If myDateTimeRage.Value = "" Then

    myDateTimeRage.Value = Now
End If

myUpdatedRange.Value = Now

End Sub

1 answer

  • answered 2019-12-13 20:31 GMalc

    Here is a basic code that works with Worksheet_Calculate

    Private Sub Worksheet_Calculate()
    Dim myTableRange As Range
    
    Set myTableRange = Range("D1:D30") 'Change the range as needed
    
        For Each cel In myTableRange 
        'when a cell is changed in the range due to a formula, the code loops through and checks all the cells for a change. 
    
            If cel.Value >= 0.01 And cel.Value <= 0.99 And cel.Offset(, 3).Value = "" And cel.Offset(, 4).Value = "" Then 
           'If the timestamp is not for columns G and H, change the offset(,3) to 10, and offset(,4) to 11
    
                cel.Offset(, 3).Value = Now
    
            ElseIf cel.Value = 1# And cel.Offset(, 4).Value = "" Then
            'Test for 100%
    
                cel.Offset(, 4).Value = Now
            End If
    
        Next cel
    End Sub