programming to play a sound when cell condition is met

I am wondering if someone can help me out with my question. I want to have excel play a sound once a condition is met. Here is my example:

I have 2 cells that take imported info. from a DDE server. Lets say one cell currently has a number of 9.00. Lets say the 2nd cell has a number of 1.00. Lets say the 1st cell over time goes from 9.00 to 8.50 to 7.33 to 6.67 and so on....it continues to go down. At the same time, lets say the 2nd cell is gaining and going from 1.00 to 1.33 to 2.00 to 2.67 to 3.33, and so on...and it continues to go up. I want to have a certain sound play once the two cell numbers "cross" each other. I only want this sound to play one time and not every "tick" as they cross and go up/down. Can someone show me how to program this?

Thank you.

In the Sheet module:

Please Login or Register  to view this content.

I feel like an idiot as I am a newbie to most excel functions. How do I enter this code?

Thanks.

Okay, so I got it to work, but I don't think I explained myself correctly. Once the numbers cross, I want a sound to be made. That did work with the code above but I also want that sound to be made when they swith back and cross at some point again (whether A1 is strong/B1 weak or B1 is strong/A1 is weak). How do I do this? Can I insert my own sound as well?

Again my mistake in writing as I mentioned before. I do not want it to keep making the sound as B1 continues to move up and A1 moves down (once they have crossed). But once they turn and both A1 and B1 cross back again, then I want to hear the sound again. I hope I am stating what I wish to do more clearly.

See my last post.

No shg, the most recent code you sent is the problem I am having now. I actually have a sound in place and it makes the sound once the cell values cross and it continues to make the sound every single time a number ticks slightly up or down. The first code you sent is more of what I am looking for but let me try to give a better example. A1 is at 9 and B1 is at 1, A1 moves down and B1 moves up they cross at some point and the sound is made. That is what I am looking for. Now B1 then continues to go to 9 and A1 continues to go down to 1. After a while A1 starts moving back up again and B1 starts moving down again. At the point that they cross again is where I would like to hear the sound again. So basically a sound is to be made in both directions when they cross. Can this be done? Also, can I put any sound in there (wav file)?

So you want the sound every time they cross?

Exactly! Even if they cross around 2 or 3 or 5 or 6 areas etc. Just when they cross back and forth of each other every time. Thank you.

OK, this goes in the sheet module:

Please Login or Register  to view this content.

... and this in a code module

Please Login or Register  to view this content.

Thank you. I was told what sheet module was but what is code module?

Open the VBE (Alt+F11), from the menu bar do Insert > Module, and paste the code in the window that opens.

That works! What if I wanted to add more numbered pairs (of A and B cells)on the same worksheet. How would this be coded?

Example:
A B
1 Set 1 9 1 once A1 & B1 cross, play sound1.wav
2 Set 2 2 7 once A2 & B2 cross, play sound2.wav
3 Set 3 6 4 once A3 & B3 cross, play sound3.wav
4 Set 4 7 3 once A4 & B4 cross, play sound4.wav
5 Set 5 5 2 once A5 & B5 cross, play sound5.wav

etc...

I would have a different sound play for each set when they cross each other every time.

Why don't you take a crack at it and holler when you get stuck?

That didn't come through like I had it planned. See doc. file attached for spreadsheet info.

I did try it and the only way I could get it to work somewhat was when I put the code in for each set manually on a seperate sheet. I could make it work manually but when I had the DDE server determine the number, it wouldn't work.

what i am suppose to do is this ,if anyone could help me out

1.Read the excel column of n rows.
2.Divide the data into 7 parts(just divide by 7)
3. assign each part a sound.

E.g.
you have data like this : 2, 4,15,23,45,2,3,66,34,12,34,5,67,88,99,55,44
Range : 2-99
Divide the range : 99-2=97 / 7= 13.85=14
At every 14th place from 2 you have a step.
So your steps
2-16=A sound
16-30=B sound
...so on

Now
when you read 2, 4,15,23,45,2,3,66,34,12,34,5,67,88,99,55,44
You should have A,A,A,B,D.....

Hence you make sounds