Now, as Macworld explains, the ability to wake sleeping Macs remotely has been extended in two vital ways with Snow Leopard as a new feature called Wake on Demand. First, the new OS allows sleeping machines to hand off Bonjour broadcast tasks (advertising services like printer sharing, web sharing, iPhoto & iTunes libraries, etc.) to an Airport Extreme base station or Time Capsule, letting the machine's services appear always-on even if the actual Mac is asleep; the Mac will wake remotely when needed by a client. This alone will allow multi-Mac homes to sleep their machines more often, saving energy and aggravation.
The second feature requires that you pair your recent-vintage Airport with a recent Mac model (all 2009 versions, and possibly some 2008 models as well): you can wake the machine over Wi-Fi, rather than just over Ethernet. If you go to System Profiler, to the Network section and the Airport data sheet -- look for a line that says "Wake on Wireless." If it's there, you've got the capability. Your mileage may vary but it's certainly fun to try waking up your machine remotely over the WLAN -- or, for fun, your spouse's machine, just to watch them jump.