Twitter makes it blink blue, and it blinks red when the battery gets stupidly low.

 

Handcent SMS is great for customizing texts. es, YGreen is the color for emails. I have changed my LED to pink for texts from general people. yellow LED for my husband, etc.. you can also customize the vibrate, amount of blinks.. it gets pretty detailed if you want it to. It also lets you customize different ringtones for different contacts as well. Also you can choose popup sms, so the text will come right to the front of your screen. i personally havent used it, but i have heard that it is well liked.

What other types of things do you want custom notifications for?
Craigslistnotifica blinks purple when something you are looking for goes up for sale..

 

download Missed Call for Android 2.0, developers name is Anno.
you are welcome.

 

I'm a former BB user who misses this functionality as well, but here's what I've done:

Missed Call - Blinking Yellow LED Light w/ Audible Reminder
SMSPopup - Blinking Cyan LED Light w/ Audible Reminder
Default E-mail - Blinking Green Light