Like most things in this world, the question of whether cloud hosting is for you is not black and white. Since you're reading this it's pretty likely that you've already read The Cloud Is Not for You and the counterpoint at Heroku Isn't for Idiots. Though both pieces are well-written and offer useful (if pointed) insight, what everyone actually wants to know is when to use each kind of hosting. If you already have experienced opinions on the subject you are probably not the audience for this post.
Shared Hosting
You'll rarely hear an experienced developer advocate shared hosting, but it really is the best solution for 95% of websites. Almost everything that gets put on the web is a set-it-and-forget-it deal, a weekend project that doesn't get a lot of traffic and doesn't need any server-side setup. If hosting your project on github doesn't make sense, just buy a $5/mo. hosting plan and be done with it. You'll never have to worry about configuring the server, applying security updates, or failover plans -- just put up your files and you're golden.
The biggest risk is that a lot of hosting providers are really crappy. Shared hosting is a commodity and so there is a lot of cost competition. For example, this site is on shared hosting on HostPC, and at the end of last week they randomly decided to turn off InnoDB. That immediately made this site fail completely, and it took a day for them to fix it. Of course, it took two days for me to notice, and that's why it make sense for me to use shared hosting -- the traffic is not high, and the site is not critical to anyone. It can handle a couple thousand users a day when something gets attention and that's all it needs. I'm on HostPC for legacy reasons, but I've used Bluehost and AN Hosting before and have no complaints with them.
Virtual Private Servers
For a long time this was the "next tier" up from shared hosting, and boy is it a jump. You know how terminals went out of style some time in the '80s when everyone realized that GUIs made their lives easier? Well, it turns out there's a certain group of people who actually like installing software they've never used before by configuring text files they don't understand by hand. These are the kind of people who enjoy memorizing endless lists of commands in order to minimize the distance they have to move their fingers. And if you want to host something that gets more than a couple thousand hits a day, you too may need to become one of those people.
Once you get over the cliff, it's not so bad. You can install whatever niche technology you want because you have complete control over your server, which is incredibly empowering (and fun!) until it inevitably leads to endless security and maintenance issues. Your Google skills will get a lot better during this time, and you will learn to love StackOverflow. In time, you will be able to do whatever you want on a fairly powerful box with few limitations, and if you use a host like Linode you can upgrade effortlessly if you realize you need more power. VPS's start at about $20/mo. and this is where many medium-sized businesses and high-traffic personal sites live. It's a very practical solution in a wide range of cases.
Unmanaged Cloud Hosting
Cloud hosting gets all the hype these days. It's basically a VPS except someone else takes care of some of the infrastructure maintenance. Cloud hosting works best for very large sites with highly variable visitor rates because it allows adding more power very quickly in response to traffic spikes. I say allows because it's up to you to write the infrastructure to actually do that, and that is not easy for beginners. If your site does not require several servers, or if you are the only person running your site, you are probably not big enough for cloud hosting to be advantageous for you.
It can also be more expensive than a VPS for small sites while providing basically the same features. Take a look at Amazon's EC2 pricing for example -- the smallest plan available is over $30/mo. and Amazon has a reputation for being relatively less expensive than other cloud hosts.
Managed Cloud Hosting
There are a couple of hosting providers out there who offer additional services on top of other cloud hosts like Amazon. Heroku is one of these. There are many that are specific to certain technologies. The pricing and utility of these services varies widely, but in general the closer you get to requiring minimal server-side configuration, the higher the price will be. This can be worth it if you want high-performance hosting that you don't have to worry about. Heroku (and some other services) actually have a free tier which is great for small apps. In most cases you'll have a lot more power to configure the server than on a shared host but less power than on a VPS or standard cloud hosting. Whether you should use a managed cloud host depends on the needs of your project, but it's hard to argue with Heroku's free tier. Understand that you are limited by the technologies they support, though in practice this is not typically much of a barrier.
Dedicated Hosting
Where shared hosting is a lot of people on the same machine sharing the same resources, and a VPS is a few people on the same machine with separate and guaranteed resources, dedicated hosting means you have complete control over an entire server. In practice this usually comes with additional maintenance guarantees and the security of knowing that no one else's code is on the machine (so there can be no "jumping the fence" and stealing your data) and sometimes hardware customization options. It's also a less flexible solution than, say, a Linode VPS or unmanaged cloud solution in the sense that it's harder to just add more power when you need it. Usually companies will buy dedicated hosting from a data center but occasionally they prefer to host the servers themselves when the security of the data is especially important. Dedicated hosting is worth considering mainly if your traffic volume is both relatively high and relatively consistent. If this is the case you should carefully consider the cost difference with cloud hosting and understand that the type of maintenance and optimizations required will be different (sometimes very different). In the end it may come down to what you are more familiar with.
Controversy
Cloud hosting is not for everyone. If you're not familiar with it and you switch from something else, you will need to adjust to different system strengths and weaknesses. Don't assume that it is right for you just because it has gotten a lot of attention. Sometimes the best option is with the quiet majority. However, the cloud does have very distinct advantages for certain types of websites with specific needs and traffic patterns. As with anything else, you will be better off if you can measure performance and price tradeoffs before committing to a new host.