Cell intercepts a value

[mamos]

How do I create an if function when a cell's value passes through or intercepts a value.

i.e. A live data input will change the cells value and I want the function to work when the value goes through value 4. i.e. cell values changes from 3.8 to 4.2.

Thanks

 

[Scott Huish]

=IF(TRUNC(A1)=4,"Do Something","Do Something Else")

 

[mamos]

Thanks for reply.

I only want the if function to occur once. i.e. the first time the cells value passes through value of 4 and not to occur everytime the cells value is above 4.

 

[Scott Huish]

It will take VBA to do that, what is it you want to do exactly when the value hits 4?

 

[mamos]

I want it to play a sound.

Currently my code works as below and plays a sound when cell M6 is greater than 4 by using the function =alarm(M6,">=4"):

Private Declare Function PlaySound Lib "winmm.dll" _
Alias "PlaySoundA" (ByVal lpszName As String, _
ByVal hModule As Long, ByVal dwFlags As Long) As Long
Private m_lngReset As Long

Function Alarm(Cell, Condition)
Dim WAVFile As String
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000
On Error GoTo ErrHandler
If Evaluate(Cell.Value & Condition) And m_lngReset < 1 Then
WAVFile = ThisWorkbook.Path & "\cashtill.wav"
Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME)
Alarm = True
m_lngReset = m_lngReset + 1
Exit Function
End If
ErrHandler:
Alarm = False
End Function