I want to run a macro when a cell value changes. I have the following
code to do this:

Private Sub WorkSheet_Change(ByVal Target As Range)

If Target.Address = "$E$5" And Target.Value = 1 Then
Call Archive
End If

End Sub

When I manually enter a 1 into cell E5, the macro runs fine. However,
E5 normally contains =IF(G5=5,1,0) so when G5 is 5, E5 is changed to 1
(auto calculate is on) but my macro does not run when this happens. It
will only run when I manually enter 1 or when I click the formula and
press enter to accept it. Why is my macro not running?