Just to clear up a couple of things on this thread. Steemax said to get Beryl. Beryl doesn't exist any more. Now it is called Compiz Fusion. www.beryl-project.org
Compiz is a great program, only if you have a video card that can handle it.
Next Gentoo is a great os as redbeard said there are many thing that it can do, although I haven't used it yet. http://www.gentoo.org/
For ease of installing and setting up Ubuntu is the way to go. http://www.ubuntu.com/
Ubuntu runs on the Debian kernel. http://www.debian.org/
The other thing I wanted to clear up is Fedora Core which is version is currently 8. http://fedoraproject.org/
Fedora Core is solely a Community based only project, which started from Red Hat. Not Ubuntu with beryl built in.
I wouldn't recommend Ubuntu Studio unless you are looking to do a lot of stuff with programs like Pro-Tools, Photoshop, or any high end video editing software.
The thing about linux in any OS is that it isn't set up by default to be flashy. It is set up to be functional first and flashy last. That being said there are my add-ons and programs that you can download for free and increase the flashiness of your OS.