Good atmosphere is related to just about every aspect of a game's production. The graphics and music are the most obvious ones, but the believability of the game-universe and the degree to which the player can relate to the character are also pretty important. Game "physics" have something to do with it too.
CV4 is a presented in a far more realistic way than Rondo. The environments and characters/enemies look believable, realistic for their time, not cartoony. And the music matches the look perfectly, which is vital to achieving the level of atmosphere in the game. Yup, a lot of the music in CV4 is moody (but not low key!), and a lot of the visuals are dark (but not dull, not murky). We're trekking through a possessed castle attempting to destroy the king of demons, after all. I think it's only fitting that the atmosphere be this poignant, unless we want the game to come off as a bit of a spoof.
There's actually a great deal of upbeat, driving music in CV4. Theme of Simon, Forest of Monsters, Pillared Corridor, Treasury Room, Bloody Tears, Beginning, and Vampire Killer are all pretty upbeat, though they still have a serious tone to them that matches the game.
As for the nostalgia thing, I don't see how we would be lauding CV4 out of nostalgia alone when we don't do the same for CV, CV2, CV3, CVA, CV:BR, and Bloodlines. After all, I played CV and CV2 long before CV4. While I didn't like CV2 much at the time, I adored CV1. And a lot of the games I liked at the time haven't held up well for me today, like the Mega Man, Ghosts N Goblins, and Wizards and Warriors series.