Even in the fantasy games, they are becoming much more based on the laws of nature. As to why so many games and mods are dealing with CT and Spec Ops in general, I'd have to say it might have something to do with the genre itself. Games are always a way to escape your reality and get yourself involved with something you might never do. Becoming a spec warrior, putting yourself in the moment and forcing the tension, anxiety, and terror is something many of us would only like to experience from a distance. A game that depicts these situations realistically only makes you appreciate our real life counterparts that much more.
People are curious animals.
For whatever their reasons, realistic games will always be appealing, especially as the technology improves. I think people who believe these games are a fad will be saying that for an awfully long time. Unreal has been here for a while now, and is obviously going to be here a lot longer. Some people, such as this team, have found it very satisfying to modify. With these factors combined with the fact that many people congregate to where the most activity is, it's not surprising these engines have a strong community.