Timestamp using VBA based on specific cell changes

matt0020190 

"the code updates the timestamp to the current time when the actual cell has not been clicked or changed"
You wrote a function, and used that function in a formula. Formulas are re-evaluated at some times even when a cell is not updated (for example, each time the workbook is opened). So a simple function + formula is not the correct design.

(Perhaps of interest to you, a function that had access to a separate store ("copy") of the values could be a valid design. But of course, that doubles the storage requirements, and you have to prevent anything but the code from updating the separate store. Too complicated.)

NikolinoDE demonstrated how code in an event handler can instead be written to react to a change in a worksheet value (due to user entry, not due to recalculation).

"Not sure on the issue but this is the error"
If you had clicked the Debug button, the VBA Editor would have opened and shown you the line of code that generated the error. (You could then take further actions to understand and possibly correct the problem.) Showing us that line of code and lines that preceded it would help us diagnose your problem.

Of course, you can use online resources to learn about debugging in the VBA Editor, such as:

• How to Debug in VBA (but no, you can't start a function or an event handler using the Start button; that's for Sub procedures)
• Use the Immediate window (VBA) 

"I want to specify the columns to check and which cell the timestamp is output"
Those are (or generally should be) specified in the event handler code, not in formulas on the worksheet.

However, VBA is very capable, and you could: a) reserve a cell on the worksheet (let's say K1) to contain identifiers for the range to monitor; b) reserve another cell on the worksheet (let's say K2) to contain an identifier for the column into which to place the timestamp; c) write the event handler code to retrieve and use the values from those cells.  Here's how that could look:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim strRangeOfInterest  As String
    Dim rngRangeOfInterest  As Range
    Dim strColForTimestamps As String
    Dim rngColForTimestamps As Range
    '
    Dim rngAffectedCellsOI  As Range
    Dim rngCell     As Range
    Dim in4CellRow  As Long
    Dim vntCellContent  As Variant
    
    '----   Retrieve information on which cells to monitor and where
    '       to put timestamps.
    strRangeOfInterest = Range("K1").Value
    strColForTimestamps = UCase$(Range("K2").Value)
    '  --   Do some error checking.
    On Error Resume Next
    Set rngRangeOfInterest = Range(strRangeOfInterest)
    On Error GoTo 0
    If rngRangeOfInterest Is Nothing Then
        Call MsgBox("Invalid range (in K1) of cells to monitor!" _
                , vbExclamation + vbOKOnly)
        Exit Sub
    End If
    On Error Resume Next
    Set rngColForTimestamps = Range(strColForTimestamps & ":" _
            & strColForTimestamps)
    On Error GoTo 0
    If rngColForTimestamps Is Nothing Then
        Call MsgBox("Invalid column (in K2) for timestamps!" _
                , vbExclamation + vbOKOnly)
        Exit Sub
    End If
    Set rngColForTimestamps = Nothing   'We don't need it further.
    
    '----   For any cells of interest that were updated...
    Set rngAffectedCellsOI = Intersect(Target, rngRangeOfInterest)
    If Not (rngAffectedCellsOI Is Nothing) Then
        For Each rngCell In rngAffectedCellsOI
            vntCellContent = rngCell.Value
            in4CellRow = rngCell.Row
            '...Update the timestamp for the row.
            If vntCellContent = "" Then '[per your code]
                Range(strColForTimestamps & in4CellRow).Value = ""
            Else
                Range(strColForTimestamps & in4CellRow).Value = _
                    Format(Now, "dd-mm-yyyy hh:mm:ss")  '[per your code]
                    '...or just = Now() if you want to store a date- _
                    time value and do the formatting in the column
            End If
        Next rngCell
    End If

End Sub

BTW, storing timestamps as text is not a good idea (takes up more storage, cannot readily be sorted or accurately compared to other timestamps).

Side note: I strongly recommend that each of the modules into which you place VBA code (standard modules, workbook module, worksheet modules, userform modules, etc.) should start with an Option Explicit statement. That statement forces you to declare any variables you use, which avoids most bugs due to misspelling of variable names.