Aerith's death scene was probably the first character death scene I ever witnessed in a videogame, so it left me pretty stunned. Everything, and I mean everything, about it was tragic. For me, the most tragic parts were the scenes leading up to it.
The dream sequence with her was one of my favorite parts in the game and I only truly appreciated it better once I learned that the girl was heading off into her doom. So that now, whenever I see how Cloud runs in an attempt to stop her, but is forever rooted to the same spot, I can feel the anguish he has from his little polygon form, to get to her and to stop her.
The music even preluding the whole event, such as the wonderful stuff we hear in the Forgotten City, gives you this feeling of foreboding; like something bad is gonna happen and it's just around the corner. Cloud, waking up in the middle of the night because he's also getting these feelings and because he's sensing Sephiroth really keeps you, as the player, on your toes.
And then you witness her death. Which, quite honestly, would have meant little to me, if it hadn't been for all the tiniest little details Square incorporated beforehand. As for 'in-the-moment' scenes, everyone talks about Cloud's speech. Maybe I'm cold when I say I cared little for it. :\ But, I've been told that many times, so it wouldn't be new.
Honestly, yeah his words were sweet and had there been decent voice acting, maybe I'd have felt their impact better, I dunno.
There were only two parts that got to me after she dies; 1) when her materia hits the ground and her theme starts to play (WATERWORKS, NE1?!) and 2) quite possibly almost every other character's reaction to her death other than Cloud, most significantly, Tifa's.
Call me a sap, but I only really felt sad when I saw Barret's still form as he cries in silence, Yuffie hugging Cloud before running off, and Tifa's reaction of sitting next to her dead body and touching her face, before running off. I say this because I had a combination of these three in my party the first few times through this part.
It was the clincher for me. No words, just little silly polygons moving about and the tragedy finally came to fruition to me.