You can't have a function reference itself. This is called circular
reference. What you can do is a worksheet change function that can detect
when either B4 or B54 is changed. I didn't know what "Y" was so I changed it
to 34

Sub worksheet_change(ByVal Target As Range)

If Target.Row = 4 And Target.Column = 2 Then
If ActiveSheet.Cells(4, 2) - ActiveSheet.Cells(54, 2) = 34 Then
ActiveSheet.Cells(74, 2) = ActiveSheet.Cells(74, 2) + 1
End If
End If
If Target.Row = 54 And Target.Column = 2 Then
If ActiveSheet.Cells(4, 2) - ActiveSheet.Cells(54, 2) = 34 Then
ActiveSheet.Cells(74, 2) = ActiveSheet.Cells(74, 2) + 1
End If
End If

End Sub




"lisa_p" wrote:

i want to add 1 to b74 each time any cell b4-b54 is equal to "Y".