The only mods I ever use are ones that replace textures, character models, weapon models, terrain, skies, etc, or just straight up add content like new cities, quests, dungeons, houses, landmasses, companions, weapons, spells, etc as long as they don't break the difficulty of the game. Sometimes I'll use gameplay changing mods if they're good, like ones that add balance to unbalanced situations, or (in Morrowind's case) improve the leveling system to make it more like Skyrim. Did you know that a mod to make Morrowind's leveling system more like Skyrim's existing before Skyrim did? Also, you know how Skyrim NPCs have schedules? How they go to work, and then go home and lock their doors at night? Morrowind didn't do that, of course, but a mod made it possible (again, before Skyrim or even Oblivion was released). There's mods that overhaul UIs and companion systems, too.
It's not about cheating, and it's not about making a "bad" game "good," it's about making an amazing game even better, even larger, and even more immersive.
So I guess this is an Elder Scrolls thread, now. My bad.