This article at gameplayer details (with links to the actual downloads) the very best game mods going around at the moment for many of the best PC games around.
"That's where we come in. Gameplayer has scoured the length and breadth of the internet to find you some of the most promising game mods for some of the best games, and we're going to take you through each one. Some are new, some are old, some are finished while others are still very much a work-in-progress, but each one is well worth a look if you're on the hunt to get the most out of your games. Read on, and have your browser ready to do some serious downloading."