Handcent SMS will also allow you to set individual notifications for texts.
I have Handcent, but every time I try to change the tone for texts, it doesn't show my ringdroided ringtones, only the ones handcent offers, I believe....anyone actually done the different tones for different contacts??
Make sure on your SD card you have a folder named "notifications".
[Ringdroid works only for ringtones I believe... notifications are separate. Confusing and pointless I know.

] edit: was corrected by alansupra94 in post below that it does work for both ringtones and notifications. Thanks!
Here's what you can do... go to your SD card, create a folder named "notifications" (all lower case) and then go to the folder where your Ringdroid files are saved, copy the one you want to use for Handcent and then paste it into the "notifications" folder. The song(s) should now show up in the list of available tones in Handcent.