Macro to play sound when condition is met

Hi there. I have a macro (as below) that plays sound when a cell value is >30. However, I would like to modiy this macro so that the sound does not play if the value changes from something >30 to another value >30. So ideally the macro would only make a sound if the value changes from something <30 to something >30.

Eg. 29 => 31(play sound) => 32 => 33 => 29 => 35 (play sound).

The cell value is continuously updating every second from Live market data (e.g. Reuters, Bloomberg). I would eventually like to be able to use the code to check numerous cells in my worksheet (e.g. A1:A1, L5:L5 and Y11:Y11) using the same criteria (i.e. >30).

Many thanks!

*******************************
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("A1:A1")) Is Nothing Then
With Target
If .Value > 30 Then Call sndPlaySound32("C:\WINNT\Media\ringin.wav", 1)
End With
End If
End Sub

******************

Option Explicit

Declare Function sndPlaySound32 Lib "winmm.dll" _
Alias "sndPlaySoundA" (ByVal lpszSoundName _
As String, ByVal uFlags As Long) As Long

Run your loop and if statements then put this in the right place.



Application.Speech.Speak "type your text here"