Preventing only one cell from recalculating

[Mike K]

Is there any way to prevent the worksheet from recalculating only if a
certain cell changes? I have code like below:


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = CELL_ENTER_DATE Then
Application.Calculation = xlManual ...


The problem is Worksheet_Change is called *after* all the formulas are
recalculated on the worksheet. Now, I know I can go to Tools/Options
and change the worksheet's calculation method to Manual, but I don't
want it to be manual. I only want to stop the auto-calc if a certain
cell changes. Does anyone know if that is possible?

 

[K Dales]

I don't know - but I think (haven't tested it) you might be able to do this
in reverse; that is, turn calculation to manual and then use the
Worksheet_Change event procedure to run the calculation unless the changed
cell is the one you want.

e.g.:
Sub Worksheet_Change(ByVal Target as Range)
If Not(Target.Address)="A1" Then ActiveSheet.Calculate
End Sub

Just suppose it might be slow if the sheet is too complex