I've been using Locale since it was first available for my G1 over two years ago, and it has never let me down. It's quite honestly the killer app for Android as far as I am concerned, not only because it's extremely useful, but because it embodies everything that Android is capable of that Apple has refused to allow in iOS.
The best part though is that you can download apps from the market that do nothing but add even more conditions and settings to Locale, such as: headphones plugged in or not; whether the phone is on a car dock; launching other programs; lockscreen passwords; and many more.
As an example of what it does for me:
Default settings: Wifi off, Ringer volume 85%, Media volume 70%, Password Lockscreen on.
It can do some really impressive stuff. During my "office hours" mine turns on BT and pairs to my BT speakerphone and sets a particular ringtone. Off-hours, it unpairs the device and sets a different one.