I am not sure it is a handcent thing, it might be but i enjoy the stock messaging app, but from my experiences when i used GOSMS back in the day there was no notification sound for a specific contact.
generally if you search all through handcent and cannot find it, chances are there isn't a feature to do that.