I love this topic! Let me give it a go.
Why has the vampire been asleep for so many centuries?
As a fan of vampires and their romanticism I sort of love your take. I would counter with the idea that he loved her, but she was killed by vengeful townsfolk who couldn't, or wouldn't, understand why she loved him. Those same townsfolk tried to kill him but he was able to escape and hide, biding his time waiting for the right moment to resurface seeking vengeance. Those who killed his love and tried to kill him are long gone but they passed down their hatred of vampires so his fight continues.
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?
No. In spite of the fact that he has been hibernating for centuries his disciples have tended to him and protected him in anticipation of this day. They have studied and learned the ways of the mortals and they are ready to teach him how to blend in with them to deflect suspicion. His ancient knowledge provides him with the know how to make use of this.
Can my vampire become human again?
Yes. Traditionalists might say no, but this is the Sims, our vampires are bound only by our imaginations.
If yes, how would I incorporate that into my story so it makes sense?
He meets and falls in love with a mortal woman, or man, who will not forsake their humanity to join him as a child of the night. They go back and forth, trying to sway the other to join them. In the end the vampire through the power of true love and remorse for throwing away the gift of humanity decides to renounce their vampirism and spend the rest of what remains of their time with their true love who has stolen their heart.
I'm a hopeless romantic as you can see.