Insert dynamic formulas into cells

Hi guys.

I am trying to write a VBA code that would insert a formula in a cell if another cell meets a condition. E.g. If R3 = "Academic" Then M3 = "n/a". Otherwise, M3 assumes the following formula:

Formula:

=IFERROR(IF(OR(K3=I3, AND(K3<I3,K3+L3=I3),OR(ISBLANK(K3:L3),L3=0), K3+L3>I3), 0, I3-K3-L3), "")

Currently I managed to get this working with the following code:

If Target.Column = 18 Then
    Select Case Target.Value
    Case "Academic"
        Target.Offset(, -5) = "n/a"
        Target.Offset(, -5).HorizontalAlignment = xlCenter
Case Else
        Target.Offset(, -5).Formula = "=IFERROR(IF(OR(K3=I3, AND(K3<I3,K3+L3=I3),OR(ISBLANK(K3:L3),L3=0), K3+L3>I3), 0, I3-K3-L3), """")"
End Select
End If

So what happens is that cell, say, M10 is using the formula based on row 3 instead of updating the formula to the current row, which is 10 in this case.

What's the best way of doing it?

I appreciate any help.

Jesus Christ it works!

millz what is this formula? How did it happen?

Thank you so very much!!

Hi FDibbins. Thanks for your reply.
And millz, thank you for your clarification.

I needed a VBA code because I wanted to prevent users from either selecting or even deleting the cell's content. Since protecting sheet isn't a viable option for this matter I thought that a VBA code would do the trick. And it did well.

I'm very happy with where I am getting with your help mates. Thanks.