Parts of Condemned were somewhat scary because of the cutscenes and dramatic lighting effects when they kick in, but during most of the gameplay and when not involved in direct combat, my mind is often wandering off thanks to some truly awful level design. That game does a great job with the sound as well so what you hear will scare you a hell of a lot more than anything you see during most of the game in my personal opinion. Its still a fantastic game, and I would recommend renting it as opposed to buying it if you're looking to get into horror games as it is single-player only and takes no more than 8 hours on normal mode before its all over. Sure, the storyline is a bit convoluted and kinda drifts off after a while, never delivering as much as it teased, but the combat more than makes up for it, and, not for nothing, its certainly left me longing to buy Condemned 2 when I have some money.
The best horror game I have ever played, however, is Bioshock. Its a lot less scary, in the conventional sense, than Condemned but its a hell of a lot more uncomfortable and disturbing. The moral concepts and storyline running through the game are dark and twisted. The only problem is, again, the game is way too easy on Normal mode, and it should be possible to get to the final boss battle with hardly any deaths at all. Plus, theres health-kits, ammo and money pretty much everywhere so its ridiculously unbalanced but still a fantastic game. Well worth buying, imo.