Heroes absolutely can be entertaining, and most of the time they're entertaining due to the circumstances the plot of the story puts them through. Which includes the role a villain plays in challenging and pushing the hero to
be a hero. Cloud is absolutely badass and an entertaining character, and that's in part due to his rivalry and overcoming what Sephiroth throws at him. The fact Cloud had to deal with the circumstance of Sephiroth and his identity being pulled from under him is what made Cloud such a memorable character, along with facing off and vanquishing Sephiroth. Saving the world from
that type of enemy, is memorable and entertaining.
Sephiroth in turn is a villain who is entertaining, charismatic and amusing in how he performs his evil. Same with Hojo. Their personalities are strong, memorable and entertaining. They do things you'll never forget, and for reasons you'll never forget. They carve a space for themselves and make every scene they participate in leave an impact.
Compare that with more mundane villains like President Shinra. They're not as entertaining because they're more down the middle. They're terrible people but without nearly the dynamic entertainment value as others. Now, Heidegger and Scarlet are kinda memorable and hilarious in their own way due to their own quirks and unique personalities too.
Umm, yeah he does? Sephiroth is motivated by hatred and desire to ascend beyond humans and reign as a deity supreme. His personality is sadistic, calculating, and cruel. He answers grief and sorrow with cold indifference and sarcastic mocking. He absolutely loathes humans after being built up as a superhero and holds a special place in his heart for Cloud to hate especially
And Sephiroth's motives are far from shallow. Until FFVII, there was never an FF villain that made it a point to destroy in order to create and attain god-hood for a new future. Kefka was an omnicidal maniac. Ex-Death lost his mind and wanted to send existence to the void. Zemus was intent on destroying all of humanity and then existence. Cloud of Darkness wished to send all existence to the void. Emperor Mateus wanted to rule the world until dying and coming back from Hell with a new thirst to destroy all life. And Chaos/Garland wanted a time-loop to live forever and suck the life out of the crystals.
Sephiroth set the stage of having villains aim for something beyond just killing all life, with a far more dynamic characterization. A trait that was built upon by villains like Ultimecia, Vayne, Caius, Ardyn etc.