I've been a long-time user of the Philips Pronto programmable remote - it's an LCD touch screen based affair, which can be programmed to the 9th degree to create your own UI of macros which correspond to lots of different activities on different remotes - eg a "Watch the TV" button which powers on your screen, starts up the satellite box, switches the TV to the right input, fires up the Amp and selects the TV audio input on that. Or "Shut everything down", where it would send "Off" commands to all your A/V kit.
To get a flavour for what's possible, just have a browse on RemoteCentral's amazing file archive ... the principal downside is you could spend hundreds of hours tweaking and tuning the setup ...
Any IR-based remote can be frustrating though, since it's all one-way - meaning, if it sends the signal to the TV to change inputs, there's no way of verifying that the TV actually acted on the command - maybe something was blocking the IR window on the screen, or maybe your macro sent it too soon after switching the TV on so it might not have started up properly.
Anyway, I started looking into the promised Sideshow Media Center remotes which we'll see later this year - remotes which have 2-way communication with a Vista Media Center PC (using the Vista Sideshow framework), so could not only control the PC and any associated A/V kit (using Bluetooth & IR), but could also let you browse media libraries or TV guides on the remote, without disturbing what's happening on the screen.
Engadget has a preview of the Ricavision remote which was on display at CES ... should be available towards the middle of the year, for around $200. I think I'll get my order in now :)