While I'm not a mechanic or anything, I've driven Colorados for a couple years and other Chevys for work. Auto is great for snowy road/mixed surface conditions when 2wd doesn't provide enough grip. From what I understand, it only engages the front axle when the rear starts to lose grip. I live in the UP of Michigan and we're currently getting dumped on with snow. I pretty much haven't taken the truck out of auto, except for on plowed highways. I only ever use 4hi or 4lo when I'm in really deep snow on backroads, never on pavement

TLDR: Auto is great when 2wd isn't providing enough traction, such as on snowy roads or uneven dirt roads. You can turn it on at pretty much any speed as long as your wheels are moving in the same direction. It works well.