Worksheet_Calculate or Worksheet_Change?

[liampog]

Hi there

I'm trying to run some code when the cell value changes to particular values.

However, the cells being monitored are protected cells that changing depending on an IF statement. The example below is the IF formula in the C2 cell:

=IF(C10="","",IF(AND(C10>"07:00",C10<"18:00"),C10,""))

I want a number of functions to run when the text in the cell changes to certain values depending on the result of the IF statement.

Do I use Worksheet_Calculate for this, or Worksheet_Change?

Can somebody give me some example code that might be a solution to this?

Thanks,
Liam

 

[RoryA]

You need the calculate event as the change event is not triggered by formula calculations - or you can use the change event but monitor changes to cell C10 (assuming that's not also a formula)

 

[Jonmo1]

You can use either...

The Calculate event will trigger when the result of a formula changes.
However, you can't capture what caused the formula to change, or even which formula changed.


You can also use the Change event.
But instead of monitoring the cell containing the formula,
you make it monitor the cells that the formula refers to.
Which in your example, would be C10


Hope that helps.

 

[VoG]

Try like this

Private Sub Worksheet_Calculate()
Static OldVal As Variant
If Range("A1").Value <> OldVal Then
    OldVal = Range("A1").Value
    '
    ' do something
    '
End If
End Sub