I think you need to download a third party app to really get the best of this option. I use Handcent SMS for texting (which is pretty cool in its own right) one of the added features is that it allows you to select music as ringtones. Once you have it, go to the contact you want to change, press edit and scroll down to Ringtone and tap where it says default. You will then get a pop up with Android System and Select Music with the blue speech bubble symbol of Handcent. You can then choose from various options.
There is also an app called Zedge which is great for downloading ringtones and wallpapers and this gives you the same choice when you select the edit and ringtone thing in contacts.
