For me it has been Fall From Heaven 2 for Civilizations 4

I haven't played too many mods, but Blue Planet: War in Heaven for Freespace 2 (FSO engine) is fantastic.

Infiltration is amazing for Unreal Tournament. By FAR my favorite mod to any game I've played.

Quite a few good ones I've played, but I think the award for the best one would have to go to Nehrim (a total conversion for Oblivion). The mod is pretty much a AAA quality game in itself that far surpasses the game it's built on.

Probably a tie between Deadly Reflexes for Oblivion and Third Age: Total War for Medieval 2: Total War. If you have Medieval 2 and love LOTR, get this mod.

DeathWish for Blood. Good lord, that was an awesome, awesome mod. Even the weakest levels were good!

Counter Strike 1.6 if that is a mod.

Europa Barbarorum for Rome Total War.

I really haven't played many mods.

FCOM for Oblivion I guess, although it is a pain to get it install and run.........I haven't played many big mods though.......

i used to be heavily into mods and pretty much try everything i could find. no longer, since i have so many games in my backlog now.

the best mod i've ever encountered was probably Nehrim for The Elder Scrolls 4: Oblivion. incredibly polished and professionally executed, you could have packaged it and put it on shelves and sold it as an official expansion...

I always went with Martigen's Monster Mod instead.

I've not really played that many big/full game type mods. I usually stick to getting a collection of smaller mods, make my own mods and tailor my own version of the game. Out of the bigger stuff I have used I think I like 1.13 for Jagged Alliance 2 most, just for the sheer amount of work and all the updates and changes they have made to the game, and anything that was not quite right or wasn't working as intended (like the New Chance To Hit system from the latest full release) I just fix myself until a new update is released.

My Selfmade Mod for "Hollywood Pictures", adding real movies and actors. :)

Don't really use any other mods.

The only mods I have installed have been for Rome and Medieval 2 Total War, as well as the new Morrowind Overhaul 2.0 a couple of days ago.

For Rome I installed Rome Total Realism, Europa Barbarorum and the DarthMod, and I spent more than 500 hours on the first, it was unbelievable, the huge map made me want to start dancing, the units were great, but the others I didn't enjoy that much because of stability issues back then and because it was heavy for my system.

A couple of days ago I installed Third Age and Stainless Steel for Medieval 2 and they look like heaven, too bad I don't have the time to plunge into their campaigns.

Same for Morrowind, game looks superb with the Overhaul but I barely get 10 FPS on the outside areas which is annoying because my system is far stronger than the minimum requirements.

FCOM is Oscuro's Oblivion Overhaul + Martigen's Monster Mod + Oblivion WarCry + Francesco's Leveled Creatures-Items.........and that is exactly why it is a pain to install.......

I'm currently playing Babylon 5: I've found her, standalone game using Freespace engine. Damn impressive. And quite realistic.