I'm trying to auto-insert a formula in column E if the user starts inputting data into one of several cells (in the corresponding row)
Below is the macro I'm using but it only seems to kick in if I edit cells in column C. Any idea why it's not working for the other 3 criteria?
Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Range(Target.Address), Range("A:E")) _
Is Nothing And Target.Row <> 1 Then
Dim r As Long
r = Target.Row
If Cells(r, "A").Value <> "" Or _
Cells(r, "B").Value <> "" Or _
Cells(r, "C").Value <> "" Or _
Cells(r, "D").Value <> "" Then
' the below adds in the formula in column E
Cells(r, "E").FormulaR1C1 = "=IF(IF(RC[-1]="",RC[-2],RC[-1])=0,"",IF(RC[-1]="",RC[-2],RC[-1]))"
End If
End If
Application.EnableEvents = True
End Sub
Bookmarks