The B737 Next Generation models (737-600, -700, -800, and -900) all use a digital cabin pressure controller, which has AUTO, ALTN, and Manual modes. Older 737's may use this digital controller or an analog controller with Auto, Standby, Manual AC and Manual DC modes.
From MyAirlineFlight.com, which also has a depiction of the older, analog panel.
In the digital panel, the Auto mode selects between two digital controllers which swap every flight so that if #1 is primary (in control in the Auto mode) this flight, #2 will be primary next flight. The ALTN mode makes the "other" controller primary, so that if the primary controller fails, the other one can be selected. With both the digital and the analog controllers, the Manual mode gives the pilots direct control over the outflow valve.
There are several reasons that this control is sometimes desirable; none of them are particularly common. First, if you need to perform a ground pressurization check, the pilot or mechanic uses the manual mode to close the outflow valve and allow the aircraft to pressurize on the ground. It isn't common, but there are some checks that require it. Second, the manual mode is used to verify that the outflow valve DOES open and close. This used to be a check performed by the pilots, but (in large part due to the Helios accident mentioned), it is now a maintenance-only function for many operators.
Third, if the flight must be conducted unpressurized, the manual mode allows this -- open the outflow valve fully, and there is no pressure differential -- say if you had a damaged window that needs to not be pressurized while the aircraft is ferried to where the window can be replaced.
Fourth, in the case of an emergency evacuation, it may be necessary to quickly depressurize the aircraft, and the manual mode allows the crew to open the outflow valve with only DC (i.e. only battery) power available. (The automatic modes need AC power to operate.)
Finally, if both digital controllers fail in flight, then the crew needs a way to control the pressurization, and the manual mode is used for this. Getting to this point is quite rare, but it can happen. Controlling the pressurization manually takes a lot of time & attention by the First Officer, so it isn't something that's practiced in the aircraft, and because the really big cues that you're doing it well or poorly come from what your ears feel, it isn't something that's done in the simulator either. But there has to be a way to manually control the pressurization, so the Manual mode is it. (Loss of all generators in flight could also put you in this position -- with no AC power the Automatic mode is unavailable. Again, an extremely rare event.)
With the removal of the outflow valve check from the pilots' preflight, the switch is typically left in the AUTO position always, unless maintenance is doing their checks. More than a few times, crews got distracted in the middle of the outflow valve check, left the switch in manual, and never got back to it. The usual result was a Cabin Altitude Warning shortly after climbing above 10,000'; if the crew recognized their error, they could simply switch back to Auto and continue. If they didn't realize that they'd done, they'd shortly get a call from the back asking why the masks had dropped, and that was usually the big clue that things had gone wrong & they needed to go back & land.
One might wonder why the Manual mode is annunciated by a green light rather than an amber one -- the amber light would show up when checking the Master Caution. That was a design decision by Boeing a few decades ago, and I don't have insight into their thinking there. (Much like, why did they use the same horn for Takeoff Configuration Warning as for Cabin Altitude Warning... it evidently seemed like a reasonable idea at the time.)