Alcina brought up a good point about overwhelming yourself if you're also starting out with scuba diving. If you are new to scuba, or haven't scuba'd in a while and need a refresher, then Auto mode would be the safest. You can get decent photos on Auto. The thing is, we are all divers before photographers whenever we're underwater, and there's no sense in adding tasks with a camera if that compromises your safety while diving.
That said, if you feel good enough about your diving skills that you can go manual, then go for it. The most striking difference I've seen between auto and manual is the color of the water column. In auto shots, the camera will think there's not enough light so it will use a slow shutter speed combined with a wide open f/stop -- this will make the background water look very light blue (or green if in freshwater) and may cause blurring with some subjects because of the subject or your camera moving and the amount of area that is in covered in the depth of field. If you shoot manual, you can force the camera to use a faster shutter speed (reducing blurs) and use an aperture that will allow more to be in focus. The background water will also be a richer, darker blue, which makes for some really stunning contrast. Again, assuming you have the help of an external strobe, otherwise the shots will just be dark in general and you're probably better off shooting auto.