Carma 2, Iron Maiden

Compass in Red Dead Redemption, during the ride home.
Snake Eater during the final battle of Metal Gear Solid 3.
Those two just off the top of my head, but I know there are some others I can't remember right now.

Also, pretty much every song that played in the game WET got me fired up, especially during Arena Battles or Rage Mode.

The Cat Lady pulled this off pretty well in a few places, I think. Least spoilery description being the end of chapter... 2? When it goes from being a quiet, rather uneventful search for an exit to crap hitting the fan and the start of a hard tune with the push of a button which really helped kick in an OHFRIGWHATDOIDONOW?! feeling to match the protagonist's. (At least it did for me.)

Hotline Miami has several, Miami Disco being the most memorable for me.

The endgame of Spec Ops: The Line.

That mission on Saints Row: The Third with Holding Out For A Hero as the obligatory background tune.

I also rather liked the menu song (and much of the rest of the soundtrack) of Gray Matter, even though I didn't really think much of the game itself.

Also, Doom.

To this day, I couldn't tell you what the soundtrack is like for Heroes of Might and Magic 5, because I played it while listening (again and again) to the first Angels and Airwaves album. I'm a fan of chilled out music when playing games, because it doesn't require much focus, so I also happily zone out on Baroque/Classical/Neoclassical music like Bach or Handel or Copland, or good melodic electronic music like Chicane or Orbital. Of course, that said, I may have put a few hours into Quake 2 and 3 while listening to Disturbed and Black Sabbath. /shrug

So many moments I can think of with many games I have played, but here are a few that I can think of from the top of my head:

Darius Gaiden - Pretty much any moment in that game had really fitting music for the crazy aquatic style of it all, but the best would be the final stage music when it starts kicking in when the boss shows up. Specifically on this stage:
http://www.youtube.com/watch?v=gJ1dMULckK0

Lords of Thunder - The whole soundtrack in that game was too good, and when it kicks in during the intro, you know you are in for a pretty metal experience.

Wing Commander - The music during the briefing sequence leading to the music played during the blast off sequence and going into space for your mission. It may seem a bit simplistic by today's standards, but really it sets the tone perfectly for when you are ready to launch off into the depths of space to blast away at some Kilrathi. I should also mention the dynamic music system for the gameplay of Wing Commander plays the perfect music at the right times so you know how everything is going down in your mission.

Cho Aniki - When this music kicks in for the mid-level boss on some stages:
http://www.youtube.com/watch?v=lbXW7ov7Qpk
It speaks for itself.

Deadly Premonition - Any cutscene that plays this song:
http://www.youtube.com/watch?v=5OUP_Z3iZPs
It just highlights the whimsical strangeness of the whole game.

Actually there is a game called Symphony that uses your music as the sound track for the game as well as the game play. You can read more about it on the Steam store or the symphonygame (.com) website.

Other than that, I don't really pay attention to the music in the game. It's there as ambiance.

I've posted it before, but the scene where Mr Crowley starts playing in Brutal Legend ranks up there as one of my favourite moments in gaming. The song and the scene went together so well, it felt like a music video - it felt epic. I have issues with elements of the gameplay but aesthetically and atmospherically I found the game to be amazing, and I think that cut scene and the bits before/after it really captured that.