This is such an interesting question, Gregg!
When it comes to collinearity of predictor variables (say X1 and X2), I like to think about the fact that the predictor variables contain overlapping information about the outcome variable Y and that the model which includes these variables gets "confused" about where the overlapping information comes from. This "confusion" can be manifested in a variety of ways and can range from mild to severe.
If our goal is to predict the outcome variable Y from X1 and X2, the "confusion" is not something to worry about (unless perhaps it's severe). As far as the model is concerned, X1 and X2 are intertwined but the model uses them in tandem to predict the value of Y. In other words, even if the model thinks that X1 contributes less/more information about Y than it actually does, it will allow X2 to soak up the remaining information so that the total amount of information about Y contained in these two variables is not lost.
But if our goal is to focus on estimating the independent effects of X1 and X2 on Y, this "confusion" is worrisome when it is not mild, since the model can't really tell these effects apart as a consequence of the fact that X1 and X2 are intertwined. As a result, the model might think that the effect of X1 is smaller/larger than it should be and will adjust the effect of X2 accordingly.
When it comes to confounding,let's imagine that Y = cholesterol level, X1 = Age and X2 = Sex. We are interested in estimating the relationship between Y and X1 but X2 is a potential confounder of this relationship. A confounder, when ignored, makes it hard to tell what is really going on.
Indeed, if we mix together males and females (in other words, if we ignore sex), we might not be able to find a relationship between cholesterol level and age. (Perhaps a relationship exists, but it is obscured by the differences between males and females.)
But if we look separately at males and separately at females (in other words, if we account for sex), we might be able to detect a relationship between cholesterol level and age.
Intuitively, comparing "like with like" (e.g., males with males, females with females) makes it easier to understand what is going on.