This is based on opinion. Please don't list it on a work's trope example list.
Nightmare Fuel / Red Dead Redemption: Undead Nightmare
You were forced to kill your family friend. Your own wife and child have become ravenous, flesh-eating ghouls, as have thousands of people across the West. Once serene (well, for the most part) settlements have become fortresses against the shambling hordes, their residents dwindling in numbers. Ammo, as well as allies, are scarce. And you've got to set out into the deadly, zombie-filled land to find a cure and possibly save who you can. And those animals that gave you such a hard time in Red Dead Redemption? They're zombies now.
Some of the random events you can come across are pretty unsettling. From a group of undead devouring a pile of corpses, civilians transforming right before your eyes, deranged cannibals who invite you for a "bite" and turn their guns on you if you decline, and more. One of the more disturbing ones is coming across a sobbing man sitting next to a dead man, likely a friend or relative. The dead man rises as an undead and the living man is forced to shoot him...right before turning the gun on himself.
New Austin and West Elizabeth feel very creepy this time around. The setting in Red Dead Redemption had many merchants, bandits, and deputies to populate the area, but in Undead Nightmare, the only thing your going to see is shambling undead clawing at your horse. Any survivors you see are probably stranded in their homes or are trying to navigate the landscape without being eaten.
It's even worse at the start of the game, where there's less zombies and less ammo. You don't know what's around the corner, your horse died, you're out of ammo, and... what are those shadows out in the plains?
Mordecai Robbard, the boss of Blackwater Graveyard, is quite disturbing. After you talk to and obtain the torch from his niece. She tells you to burn Mordecai if you see him. After killing enough undead after burning the first coffin, he will rise from his grave. He is much bigger than all the other zombies, which can come off as a scare to new players given only normal undead have been encountered at this point of the game. And as he rises, he says, "Ain't you a sweet little lady," while making beast-like roars. It implies that he could have raped her niece when he was alive.
After you clear out Blackwater, a group of people are discussing their current situation, when a bolter walks up to one of the women and eats her alive from behind. To make matters worse, if you let it finish feeding on her, it will start rabidly scurrying towards you making horrid screeching noises and will scratch you up once it gets to you.
Bolters are easily the scariest of the four zombie types. They savagely scurry on all fours, making them difficult to target, while usually making inhuman screeches (other times low growls similar to those of Bruisers) when pursuing you. Once they get up they claw at you at rapid succession, faster than the attacks of other undead, so if you see a zombie hunched over and limping, watch out.
Tall Trees in Undead Nightmare is easily the scariest area in the game. Terrain is rocky, trees get in your way when you travel, zombie bears roam here as do zombie wolves. Think you're safe on your invincible zombie horse? You are until a bolter you can't see pulls you off your horse in the blink of an eye to make you a meal for zombie animals. Plus sasquatches lurk here and while they are harmless they tend to let out a bloodcurdling scream or start talking in a panicked voice that is also pretty scary. And the worst part is that it is so dark you can't see a damn thing which makes not being attacked terrifying plus you're now left shitting yourself wondering when you will be and from what direction or distance.