The entire finale of IV is simply amazing. From the Clock Tower to the slow ascent to Dracula's tower with Beginning playing as a BGM, leading up to some last battles against Slogra, Gaibon, and Death himself before you fight Count. It's all just too good and forever engraved in my brain as one of the best moments in the series.
After defeating Elizabeth Bartley in Bloodlines and hearing Simon's theme play before fighting Dracula was pretty awesome too.
I have to agree. Castlevania 4 made that part(well every part really) really memorable. Then after you beat Death, you climb the stairs, go THROUGH A ROOM which has torches that light up as you pass them, and you find Dracula's chamber, with an open coffin in the background.
The ending is my favorite as well, but that's a bit off topic, so I won't go there.
Chaos Realm in Aria of Sorrow deserves honorable mention as well. I mean, it's just a combination of weird versions of various rooms you've already been too, with different challenges in them, but the overall atmosphere, and creepiness of it, it stands as pretty memorable for me as well. You see space getting all warped in the background of some rooms and everything's so colorless. There are even some new freaky enemies that only exist there. Then at the end, everyone gives you a pep talk, and off you go to fight evil incarnate! I think it's a really cool climax to the game. Mind you, this all happens right after you've proven yourself by fighting a Belmont!