Heres a bunch of mods that i've found that well..they just aren't scary...at all...
1.)The Mansion 3:(Scaryness: 4/10-eerie)- Freeman Residence ( lame mod, only makes u jump on one part, mabye. )
2.)Gm_House_of_horrors:(Scaryness: 2/10,it almost tried to scare me)- basically its buggy as hell, its supposed to be a spinoff of Nightmare House, but it looks as if it was made by a kid. I couldn't help but noclip my way through this map, and even then i got lost...and it basically wound up as a waste of 15 minutes.
3.)Stay Alive!:(Scaryness:1/10, staying alive is more painful then dieing.) - basically its just endless halls, blank halls, of hl2 zombies. oh and Resident Evil waiting themes play in the backround. the only thing i could reccomend here is turn your brightness settings up to its max, and mabye replace the music with something else. Using impulse 101 might also help make it at least worth a 2/10.
4.)Slums 2(Scaryness: 2/10, scaryness right from the slums....discount scaryness?)- I think this is supposed to be a horror mod, but i really can't tell between the endless crossing of fighting combine..then more zombies..then combine..then zombies..and the ending of you screaming as a stalker walks over your body in a train tunnel trying to pass off as a "horror" ending..well it just isn't scary..
if you have any suggestions for something to put on here, it'd be great XD