Why has the vampire been asleep for so many centuries?
I like your vampire hunter idea. Maybe, instead of causing his demise, the love interest relented and begged the hunter boyfriend to not kill him. Instead, they imprisoned him in his coffin in his castle. (See Dark Shadows. Barnabas Collins was imprisoned in his casket with a cross nailed to the lid and the coffin chained shut).
Or, he was mortally injured but was able, through his vampiric powers, to place himself in a stasis until a cure could be discovered. His staff attends to him and protects his resting place.
Does my vampire sim need to be super ridiculously pale like Vlad? Or can he be a tad more healthy looking and not so gray?
Nah, he can be whatever you want him to be. You could use the dark form for a more pale version of the vampire if you want.
Can my vampire become human again?
Sure. With the right motivation (love?) that could actually become his story quest. In game, there are ways to remove the vampire curse that requires skill development, so that would be a great end goal for him.
If yes, how would I incorporate that into my story so it makes sense?
He falls in love with a modern mortal woman. She is light and he is dark, and he doesn’t want to subject her to the curse of vampirism. In addition to navigating a modern and unfamiliar world, he goes on a quest to remove the curse from himself so they can be together. In game, he has to become level 15 in Vampire Lore which unlocks a recipe for a curse removal potion, which is made at a bar. He might also choose to avoid feeding on people because of her influence and take plasma fruit instead. It can be a story of his redemption.