So the other day I was watching Entourage, and during the last scene Ari tells Vince that he's got a potential job offer to be a studio head and get Vince any job he wants. Vince answers back saying that it's great, but he thought they would end what they started. As he says the line, the Radiohead's Fake Plastic Trees qued in the background, and portrayed the uncertainty that both Vince and Ari were feeling in the scene.
One memorable musical moment that I remember was from an episode on Smallville (Season 1). It's when Whitney's dad (Lana's then boyfriend) passed away. The scene had a lot of tension between characters (Lana, Clark and Chloe) and you can clearly see it as they made their gazes to each other.