As much as we'd like to give a one-word answer, this is one of the trickiest questions in digital photography, so we'll provide a short answer and a long answer.
The short answer
For any images you plan to print or retouch, we recommend you stick with a resolution between 7 and 12 megapixels; Web- and e-mail-only photos should be fine at resolutions less than that. Most digital cameras these days have sufficently high resolution that you don't need to worry about too few pixels. You may need to worry about too many, however, since the resulting larger file sizes can result in slower shooting for point-and-shoot models.
The long answer
It depends upon what you're doing with the picture and how you're doing it.
Hint: If you're going to display or print pictures at smaller-than-actual-pixel size, resolution doesn't matter much--opt for the camera with the best color.
The images below were shot with a 7-megapixel and a 10-megapixel camera, respectively, and scaled down to smaller-than-actual size. Aside from slight color differences, they should look pretty similar.
 7 megapixels | 
10 megapixels |
Let's say you want to blow up a detail shot or print a picture; that's where extra resolution comes in handy, for both printing and Web display. The 8-megapixel shot (left) is a bit blurry, but the 10-megapixel (middle) and 15-megapixel (right) shots come pretty close to each other. Which could you live with?

8 megapixels | 
10 megapixels | 
15 megapixels |
Now, suppose you want to crop in tightly on that "20." That's where higher resolution comes in handy. Once again, here are the same shots, cropped so that the "20" fills the same size space.
 8 megapixels |  10 megapixels |  15 megapixels |
The 8-megapixel shot (left) loses some cohesion, but the 10-megapixel (middle) and 15-megapixel (right) shots are pretty comparable to one another. (The 10-megapixel shot is higher contrast, so the uniformity of the straight lines in the higher resolution shot can get a little lost visually.) The key is knowing how close you're going to want to get.