It's only scary if you play it in the dark, alone and on the hardest difficulty where you actually have to run from certain fights and can't just destroy everything before it gets near you. I found the first one far more terrifying, but that is because I did what I stated above. The second one I played with my fiance on the couch next to me on the easiest difficulty because she was getting bored of watching me die and just wanted to see the story. So that most likely detracted from the "scary" element.
That being said, I find the concept of Co-Op detracting from the "horror" part. Instead of making it Co-Op, they should of allowed people to drop into your game and place as infected, like L4D Versus. Would make for more interesting game play and a better challenge for those that wanted it. Although I suppose the majority of people would just toggle it off to prevent being owned by other players.
Wasn't there another game that did something similar though? Dark Souls for the PS3 or something?