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.
Response: The short answer | The long answer
The short answer
For any images you plan to print or retouch, we recommend you stick to 4-megapixel resolution and higher; Web- and e-mail-only photos should be fine at resolutions below that. For prints 8x10 or larger, look for digicams with 5-megapixel resolution or higher.
The long answerIt 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 4-megapixel and a 6-megapixel camera, respectively, and scaled down to smaller-than-actual size. Aside from slight color differences, they should look pretty similar.
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 4-megapixel and a 6-megapixel camera, respectively, and scaled down to smaller-than-actual size. Aside from slight color differences, they should look pretty similar.
4-megapixel
6-megapixel
Hint: If you're going to crop in close or print large, higher resolution lets you crop in closer and get better prints.
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 4-megapixel shot (left) is a bit blurry, but the 5-megapixel (middle) and 6-megapixel (right) shots come pretty close to each other. Which could you live with?
4-megapixel
5-megapixel
6-megapixel