Solved: Copy formula automatically & Paste valuse :VBA code error Rectification

[satish gubbi]

Hi
I have below code that needs to be rectified, while its getting executed automatically, I am getting message as below.

And this code is copying formula, I need this to copy only values (result of formula which is not working here.

attached file for your reference. Kindly help

Error message
Run time error '13':
Type mismatch


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iRow As Long
With Worksheets("Email Tracker")
If Target.Columns = "Completed" Then
iRow = .Cells(.Rows.Count, "F:F").End(xlUp).Row
With .Range("H5", .Cells(iRow, "H"))
.Formula = "=IF(RC[-2]=""Completed"",NOW(),"""")"
.Value = .Value
End With
End If
End With
End Sub

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iRow As Long

On Error GoTo ws_exit

Application.EnableEvents = False

If Not Intersect(Target, Me.Columns("F")) Is Nothing Then

With Target

If .Row > 4 And .Cells.Count = 1 Then

If .Value = "Completed" Then

With .Offset(0, 2)

.Formula = "=IF(RC[-2]=""Completed"",NOW(),"""")"
.Value = .Value
End With
End If
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

[satish gubbi]

thank you very much xld, this code working amazingly.