I am trying to insert a formula into a cell but it is not working. Any help would be very much appreciated. This is the code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C:C")) Is Nothing Then Exit Sub
    Cells(Target.Row, 5).Formula = "=vlookup(target,Sheet2!$A$5:$B$30,2,FALSE)"
End Sub