Modded games, total conversions, or full standalone games seem to be some of the only things I play these days.
Most recently, I decided to get a joystick and gave Diaspora a spin. It's a standalone game on the Freespace engine set in the re-imagined Battlestar Galactica series. Honestly, it's amazing how much it feels like the series. Captures the space battles and what I wanted from a BSG game perfectly.
On a similar flight sim note, is X-Wing Alliance. The game itself has some fantastic HD model and backdrop mods which make the game look considerably more modern, but there are also straight up remakes of both X-Wing and Tie-Fighter for it, making it 3 massive games in one.
You did mention Half Life 2, but Half Life 1 and GoldSRC in general has a decent modding communitty. There is a pretty impressive HD content pack for Half Life 1, it's expansions and many of the popular campaign mods like Azure Sheep called the Trusty packs. Then there's also Cry of Fear, a standalone survival horror game that is completely free and is one of the better survival horror (and Silent Hill, which it is heavily inspired by) games to come out in recent memory.
Bethesda games are always have very active modding communities. Hell, Skyrim was only really playable for me after installing hundreds of mods. Hell, modding these is practically required with all the community patches and fixes you need.
Battlefield 2 has extensive modding and mod support too. Notably Project Reality and Forgotten Hope 2, which are the only 2 military shooters you need, complete with 100+ matches and bot support for most maps.
Dawn of War, already mentioned. Although most of it's mods these days are basically compilations of parts of older mods, it's still going decently strong, and practically every race and thousands of maps are now available for it.
The Total War series, particular Rome and Medieval 2, are still extensively modded to this day. My favourites are Roma Surrectum for Rome (IMHO the best mod for that game), and Third age with (a customised) MOS submod, being the best Lord of the Rings experience out there.
By far though, the most active modding community I have seen is the one that started it all - Doom. Doom has active mod development on multiple sites to this day, and over the decades countless gameplay and map mods have been created. Here's just a slice of some of the greats: