The most recent example of this was hearing The Chemical Brothers song, "Alive Alone" (vocals by Beth Orton) during a key moment in the Virtuality pilot (which I actually liked a lot). I was familiar with the song (Exit Planet Dust is an awesome album, to state the obvious), but I can't say that it had any special grip on me. But after seeing it used in the Orion drive detonation sequence (at the 50 minute mark if you watch it online), it made me like it a whole lot more.
This isn't the first time this has happened. Other times include:
* Seeing Soul Coughing's Super Bon Bon during the cold open sequence of an episode of Homicide: Life on the Streets
* When the Propellerheads' Spybreak! plays during the lobby shootout in The Matrix.
* The epic use of Inna Gadda Da Vida during the climax of Manhunter. If you know the scene, nothing more needs to be said. If you don't, well, I pity you.
If the director knows what he or she's doing with the soundtrack, the song will match the tone and content of the scene -- they're using the song to reinforce the impact of the visual and action. (And I guess it also happens with music videos, so nothing new there.) But I still find it interesting how the opposite effect occurs, whereafter you associate the song with the scene.
I guess I'm easily impressed.
If you've got an example of a song that you ended up liking a whole lot more after seeing it used somewhere, feel free to share.