I have this VBA that works very well with the worksheet change but when I use formula in the range stated, it doesnt work when there is a change in formula result then it will change all the cell with formula to non-formula cell value.
Any one able to help so the VBA able to work on the range with formula.
I want to make range D:D fill with formula (e.g. A1/B1)

Private Sub Worksheet_Change(ByVal target As Range)
    Dim rng As Range: Set rng = target.Parent.Range("D:D")
    Dim X As Variant
    If target.CountLarge > 1 Then Exit Sub
    If Intersect(target, rng) Is Nothing Then Exit Sub
    If X = 0 Then
        On Error Resume Next
    End If
    X = target.Value '<<<<< save the new value
    With Application
       .EnableEvents = False
       .ScreenUpdating = False
       .Undo '<<<<< restore the previous value
    End With
    target.Offset(, 1).Resize(, target.Value).ClearContents
    target.Value = X '<<<<< restore the new value
    target.Offset(, 1).Resize(, X).Value = "OK"
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub