I used to love the story, even would say RE4 is my favorite one in the series, with 2 coming in a close second. RE5 put a big damper in things, especially bringing in the co-op, which I couldn't stand. A lot of things about that game bug me. RE6 was a step in a better direction than RE5 was. I personally thing they should have stuck to Leon or Chris in RE6 instead of adding in all the different stories. It ended up feeling like several half games packed into one.
The saddest part is I used to love horror games, not so much more though. Its more action packed, which dampers things a lot. The stories are getting worse, along with level design. This goes for Silent Hill too, I used to love it just as much. Horror games in my opinion or basically dead, things such as Dead Space, are boring and mediocre at best.
Simply put, you don't get horror when you go in guns blazing, with more than enough ammo. Just my opinion though.