There are 2 games I absolutely adore with mods; Minecraft and Skyrim. With Minecraft, I mostly use Feed the Beast because that mod pack is absolutely amazing and adds thousands of items and even more to the game, making the playability longer and just in general more enjoyable. Sometimes I go back to the normal Minecraft and it just seems quite lame compared to using a variety of different mods. You normally use a range of mods in Minecraft rather than just one, to make the playing experience a lot better and people have actually put together mods that compliment each other, so to speak, and it's just easy to download and start playing. There are quite a few mods that don't even add much to the game, just add small things to the game that are useful and help us to carry out certain tasks. Since there are thousands of mods and so many that I like, not to mention texture packs, I'll name a couple I love: IndustrialCraft, BuildCraft, ThaumCraft. Those are the ones that come to mind straight away.
Skyrim mods also have variety. I won't talk too much here because I rambled on about Minecraft mods. Skyrim mods are generally very enjoyable, a lot of the mod producers are very creative. I've once used a mod for a lingerie shop. Yes, a lingerie shop with posters or Argonian models on huge posters and mannequins with lingerie displayed. That's just one of the more humorous examples and there is a lot more where that came from. But, yes, there are very good and fun mods out there that are useful and add to the game. I really can't remember any names of mods in Skyrim I just remember what the mods I use add to the game. I really enjoy using more weapons and ones that add expensive houses and caves around the map.