Calculate when cell changes

[cmb80]

Hi all, probably a bit of simple coding - but I would like my Excel sheet (set to manual refresh) to calculate when cell value "Y2" changes.

Y2 is controlled by a data validation drop down, when the users changes the value I would like the sheet to refresh - rather than pressing F9.

Thanks in advance

[tom1977]

Hi
try this in module of worksheet

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) = "Y2" Then
Application.Calculation = xlAutomatic
Else:
Application.Calculation = xlManual
End If
End Sub

[Paul]

Hi Cmb, the following code, placed within the specific worksheet's code module, should work for you:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("Y2")) Is Nothing Then ActiveSheet.Calculate
End Sub

[cmb80]

Hi both, great they do what I asked. My next problem (and one I didn;t factor in) is that I also want the same thing to happen if data is changed in cells Z58:Z81.

I assumed I could just copy the code you have provided above and add it to the bottom with the new range - but it doesn;t appear to work.

Any help on that?

Thanks again

[tom1977]

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Union(Range("Y2"), Range("Z58:Z81"))) Is Nothing Then ActiveSheet.Calculate
End Sub