Agreed on the Kingdom Hearts games. They're pretty cheerful and light-hearted until you reach the last couple of worlds... and then if you feel like revisiting the previous worlds, you realize how boring and empty they are because there's no plot developing in any of them. Just enemies spawning over and over while you sweep through them with your overpowered party. And of course, there's the fact that the tone becomes pretty somber and, uh, climactic. I think in the case of the first game, the game is at its most fun when you're teleported blind into Traverse Town and Night of Fate is still playing. That's the last time you ever hear Night of Fate in the whole game, which depresses me. It's like the magic's over.
On a less heartfelt note, I love the beginning of Resident Evil 4, but it starts going downhill when Ashley is introduced, and it definitely loses color once you leave the village and enter the castle.
Hmmm ICO comes to mind too. Once you part ways with Yorda the game's over for me. And in Shadow of the Colossus, the last 4 or 3 colossi I always fight wearily, because I hate the game's ending and the dreary atmosphere gets me.