Many great games have built-in support for mods (Oblivion/Fallout 3 being primary examples), and some are easily modded unofficially. They really increase the replay value of a lot of games, remove annoying issues, etc. Overall, they're almost always worth looking into.
My interest in this topic has increased recently due to my recent (re-)discovery of a very in-depth mod for Diablo II: LoD called Eastern Sun. (If you're a fan of Diablo and want an excuse to squeeze more hours out of it, here's a list of features
http://miyoshino.la.coocan.jp/eswiki/?Features)
My question to you is this: what mods have stood out to you as some of the greatest (for any game)?
Windows 7 Home Premium Intel Pentium Dual-Core 1.6 Ghz 2GB DDR2 RAM GeForce 8600GT Twin Turbo