Kevin
It depends upon the situation. With my CP5000 in normal outdoor shooting, auto works fine, compensating for moving from direct sunlight to shadow, clouds passing over and so on. It is limited in its range so it won't neutralize a sunset. However, for a sunset, I would certainly switch to "Fine"=daylight balance and possibly crank in a bit of warming as well. I have heard complaints that while inexpensive point-and-shoots handle a much wider range, they make it impossible to shoot a sunset.
Due to its limited range, auto does not work for incandescent, most fluorescents, mixed light and most high intensity lights.
The Incandescent setting gives excellent balance with 3200K photofloods. With household bulbs one can dial in a bit more coolness and pretty much nail it for the specific room. Incandescent at +3 does a reasonable job with the ubiquitous toxic-orange streetlights, though the best I have yet achieved is with the Incandescent setting and an 80B colour conversion filter. One can use fine tuning to get even closer.
For mixed light, light in a room with strongly coloured walls or other reflective surfaces, a manual preset white balance is necessary. Read off a white or grey card at subject position with the card pointed at shooting position. An incident reading through a neutrally coloured plastic diffuser at subject position works well too. I use the top of a Pringles potato chip can for very accurate balance.
Fluorescents and high-intensity lights also require a manual white balance. I have not found the fluorescent settings matching any fluorescents I have ever tried. Results of shooting under light sources with discontinuous spectra are similar to that of film. Since the spectrum is made up of peaks and valleys, quality of colour depends entirely upon how well the spectrum of the light matches the colour response curves of the sensor. Unlike film, a preset manual white balance will likely result in better colour than any attempt at balancing a film with decamired or other colour correction filters unless a custom filter can be created to match the specific unit's spectral characteristics to the film. In essence, this is what the manual white balance attempts.
So the answer is "all of the above" dictated by the environmental factors of the shot.
A reasonable follow-up question might be, "Would it not be better to do this in processing?" All colour balancing in Photoshop or other programs is somewhat destructive. Shooting RAW and processing within 48-bit workspace exclusively, will do the least harm. However, starting out with a well balanced exposure is by far the most desirable.
larry!
ICQ 76620504
http://www.larry-bolch.com/