It may depend on what version of the OS you're using, but I can tell you how with OS 5:
1. From the home page click on the "SOUNDS" icon
2. Scroll down to the bottom of the "Select a Sound Profile" pop-up dialog and click on "Set Contact Alerts"
3. At the top of the next dialog, click on "Add Contact Alert"
4. Press the BlackBerry button (the one with the BlackBerry dots) and select "Add Name"
5. This will bring up your contact list and then just select the contact you want a custom tone for and click on it. This will add that contact to the "Contact Names" field for you.
6. Then you can click on either "Phone" or "Message" and set a custom tone there.
7. When you've selected and set your tone(s), then hit the Return button or Back button on your BlackBerry and select "Save"
Now when that contact sends email or calls (depending on what you setup) it will use that custom setting instead of the default setting.