On the "Bram Stoker's Dracula" soundtrack, there's a great tune called Vampire Hunters. I once played that when the PCs were making their way up to Castle Ravenloft. Set the tone quite nicely.
Also, decided one time to pull out all the stops in my Star Wars d6 game and tried to script it like the movies. When everyone was ready to play, I hit the Main Title (complete witht he 20th Century Fox trumpets right at the beginning) and handed out papers with the opening title and blurb written out in the same scrolling-away-from-the-screen style as the movies. And at the climactic battle played the Imperial March etc etc. Pure cheese, but pure fun.
But only did it once. As another poster mentioned, keying certain songs to a certain scenes is a little unweildy...for me anyway.
These days I just have some quiet background music playing that suits the mood of whatever we're doing that night. Sometimes the Indiana Jones music, sometime LOTR, sometimes Dracula (That whole sountrack is pretty awesome) Never anything with lyrics though.
Now that I think of it, I should look into a Pirates Of the Carribean one.
Some day, just for yuks, I'd like to pop in a copy of the old (original show) Star Trek hand-to-hand fight theme for when combat starts. Anyone know what that tune is called?