Like many backpackers I know, my first sleeping pad was a cheap blue foam closed-cell pad. But after a few uncomfortable nights I looked for something better and soon discovered the added comfort of sleeping on a self-inflating sleeping pad. I owned and used a Therma-Rest Guidelite and later a Therma-Rest Ultralight. Then about 2 years ago I discovered lightweight insulated air mattresses and tried out the Exped Down Air Mat, the Insul Mat Max Thermo, and the Big Agnes Insulated Air Core. Of the three types, I still use them all, depending on the circumstances.
Closed Cell Pads are great for providing insulation, but not so great at providing comfort. They also tend to be bulky, but they are the lightest of the three. Self-inflating sleeping pads like Therma-rests can be pretty compact and lightweight, and they do a decent job at insulating you from the cold ground so they are a nice compromise. Air mattresses tend to be the comfiest of the three but they typically don't do as good a job at insulating you from the cold ground, unless you get the ones that have added insulation. They are similar to self-inflating sleeping pads in weight and compactness.
Most of the time when I'm backpacking/camping, I bring along my Big Agnes insulated air core pad. It is very comfy and good down to about 15 degrees. In the winter, especially when camping on snow, I bring along my closed-cell foam pad, a Z-Rest, and add my Therma-rest UL on top of it. You'll find many people who use 2 pads in the winter for added comfort and insulation.