The Silence Of The Lambs terrified me the first time I saw it. I could have only been about fourteen. It was Anthony Hopkins that did me in. I remember when Clarice reached for the towel, I was whispering things like "don't do it, he'll rip your arm off!" He didn't, of course, but I was still petrified that he might. Prior to then, all the horror movies I'd seen were of the predictable, slasher variety. The Silence Of The Lambs was completely different. I didn't know what to expect and that made the scary factor ten times worse! I thought Red Dragon would scare me when I watched it at the movies a year or two later. It didn't, apart from some of the scenes with Ralph Fiennes.
The last movie that scared me was probably the Exorcism of Emily Rose. Why? Emily Rose's room at university looked just like mine! I had these visions of Satan crawling into my body and couldn't sleep properly for at least a few nights afterwards.
None of M. Night Shyamalan's movies have particularly scared me. I still enjoy them, though.