Run macro when cell value changes

[SQUIDD]

Hi All

I have tried to research this, but not found anything to work completly.

I want to clear some cells if a cells value changes, but the cells value is the result of a formula.

Posted below is what i have tried, but i get "out of stack space", so guess im looping continuesly.

im guessing actually the sheet caluclates again when i set it to 0??

Private Sub Worksheet_Calculate()
    If Range("C38") = 1 Then
        Range("C28") = ""
        Range("E28:E33") = ""
        Range("C38") = 0
    End If
End Sub

thanks dave

 

[rollis13]

Have a try temporarily disabling events:

Option Explicit
Private Sub Worksheet_Calculate()
    If Range("C38") = 1 Then
        Application.EnableEvents = False
        Range("C28") = ""
        Range("E28:E33") = ""
        Range("C38") = 0
        Application.EnableEvents = True
    End If
End Sub

 

[SQUIDD]

Hi rollis13

Genius mate, worked perfectly, so simple but very effective. Feel a little silly now LOL

thanks ver much

Dave

 

[rollis13]

Glad having been of some help .
But I'm not sure that the problem was create by C38=0, instead, probable by other formulas in other cells that dipend on it.