Playing a sound on cell value

[graemeal]

I have an automated sort descend happening all day. The top several cells are coloured green and if a cell jumps to the top from below the green cells, I would love a warning sound. Is is possible to activate a sound if a cell changes from a colour to a non colour. Preferably if any of the cells within a range of say F2: F6 changes from green to a non colour.

If it is not possible to have a change of colour to activate the macro, I could get around it by - if any cells in a range of H2:H5 >than 1 it plays a sound

Cheers

XP XL2003

 

[graemeal]

Thank you kindly again

 

[graemeal]

Sorry, I think I may have explained it badly. I was after a differant sound being played for each range. Such as say when P2:P5 range is triggered the intel.wav plays. And when the P6:P12 range is triggered the ringin.wav plays. Again thanks. No idea how you learn all this stuff. A week of intense research, trial & error and still no further advanced.

Private Declare Function PlaySound Lib "winmm.dll" _
Alias "PlaySoundA" (ByVal lpszName As String, _
ByVal hModule As Long, ByVal dwFlags As Long) As Long
 
Const SND_SYNC = &H0
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000
 
Private Sub Worksheet_Calculate()
Const FName1 As String = "C:\intel.wav"
Const FName2 As String = "C:\windows\media\ringin.wav"
Dim h As Range
For Each h In Range("P2:P5")
    If h.Value >= 1 Then
        Call PlaySound(FName1, 0&, SND_SYNC Or SND_FILENAME)
        Exit For
    End If
Next h
For Each i In Range("P6:P12")
    If i.Value >= 1 Then
        Call PlaySound(FName2, 0&, SND_SYNC Or SND_FILENAME)
        Exit For
    End If
Next i
End Sub

 

[mole999]

I'm confused, I'm sure this does play diiferent sounds for each range, they only rely on the sound identified to exist on the paths identified, if either don't match then no sound

 

[graemeal]

Ok I see what you mean. Thanks kindly I will play around with it. It was 4.00am in the morning

 

[VoG]

In testing this did play sounds related to both ranges. In fact, that's why I changed it to SND_SYNC so that if the condition was met in both ranges then both sounds would play (and they did).

Make sure that you copy the exact code that I posted - Exit For makes a big difference compared to Exit Sub.

 

[reyrey2135]

I would like to know something along the same lines. I would like excel to play a wave file when A11 is greater than D11. I would also like it to play a different wav file when D11 is greater than A11. Any help would be appreciated

 

[VoG]

I would like to know something along the same lines. I would like excel to play a wave file when A11 is greater than D11. I would also like it to play a different wav file when D11 is greater than A11. Any help would be appreciated

How do these values change - formula or entered value?

 

[reyrey2135]

They change based on a simple sum formula

 

[VoG]

Right click the sheet tab, select View Code and paste in

Private Declare Function PlaySound Lib "winmm.dll" _
Alias "PlaySoundA" (ByVal lpszName As String, _
ByVal hModule As Long, ByVal dwFlags As Long) As Long
 
Const SND_SYNC = &H0
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000
 
Private Sub Worksheet_Calculate()
Const FName1 As String = "C:\intel.wav"
Const FName2 As String = "C:\windows\media\ringin.wav"
If Range("A11").Value > Range("D11").Value Then
    Call PlaySound(FName1, 0&, SND_SYNC Or SND_FILENAME)
Else
    Call PlaySound(FName1, 0&, SND_SYNC Or SND_FILENAME)
End If
End Sub

Change the sound file names in red to suit.