I pick up your remarks about sheaves. Indeed, the sheaf condition is a very good example to get a geometric idea of a limit.
Assume that $X$ is a set and $X_i$ are subsets of $X$ whose union is $X$. Then it is clear how to characterize functions on $X$: These are simply functions on the $X_i$ which agree on the overlaps $X_i \cap X_j$. This can be formulated in a fancy way: Let $J$ be the category whose objects are the indices $i$ and pairs of such indices $(i,j)$. It should be a preorder and we have the morphisms $(i,j) \to i, (i,j) \to j$. Consider the diagram $J \to Set$, which is given by $i \mapsto X_i, (i,j) \mapsto X_i \cap X_j$. What we have remarked above says exactly that $X$ is the colimit of this diagram! In a similar fashion, open coverings can be understood as colimits in the category of topological spaces, ringed spaces or schemes. It's all about gluing morphisms.
Now what about limits? I think it is important first to understand limits in the category of sets. If $F : J \to Set$ is a small diagram, then we can consider simply the set of "compatible elements in the image" of $F$, namely
$X = \{x \in \prod_j F(j) : \forall i \to j : x_j = F(i \to j)(x_i)\}$.
A short definition would be $X = Cone(*,F)$. Observe that we have projections $X \to F(j), x \mapsto x_j$ and with these $X$ is the limit of $F$. Now the Yoneda-Lemma or just the definition of a limit tells you how you can think of a limit in an arbitrary category: That $X$ is a limit of a diagram $F : J \to C$ amounts to say that elements of $X$ .. erm we don't have any elements, so let's say morphisms $Y \to X$, naturally correspond to compatible elem... erm morphisms $Y \to F(i)$. In other words, for every $Y$, $X(Y)$ is the set-theoretic limit of the diagramm $F(Y)$. I hope that this makes clear that the concept of limits in arbitrary categories is already visible in the category of sets.
Now let $X$ be a topological space and $O(X)$ the category of open subsets of $X$; it's an preorder with respect to the inclusion. Thus a presheaf is just a functor $F$ from $O(X)^{op}$ to the category of sets (or which suitable category you like). Now open coverings can be described as certain limits in $O(X)^{op}$, i.e. colimits in $O(X)$, as above. Observe that $F$ is a sheaf if and only if $F$ preserves these limits: If $U$ is covered by $U_i$, then $F(U)$ should be the limit of the $F(U_i), F(U_i \cap U_j)$ with transition maps $F(U_i) \to F(U_i \cap U_j), F(U_j) \to F(U_i \cap U_j)$, i.e. $F(U)$ consists of compatible elements of the $F(U_i)$, meaning that the elements of $F(U_i)$ and $F(U_j)$ restrict to the same element in $F(U_i \cap U_j)$. Thus we have a perfect geometric example of a limit: the set of sections on an open set is the limit of the set of sections on the open subsets of a covering.
Somehow this view takes over to the general case: Let $F : J \to Set$ be a functor. Regard it as a presheaf on $J^{op}$, and the map induced by $i \to j$ in $J^{op}$ as a restriction $F(j) \to F(i)$. Also call the elements of $F(i)$ sections on $i$. Then the limit of $F$ consists of compatible sections. Since I've been learning algebraic geometry, I almost always think of limits in this way.
Finally it is important to remember that limit is just the dual concept of colimit. And often algebra and geometry appear dually at once, for example sections and open subsets in sheaves. If $(X_i,\mathcal{O}_{X_i})$ are ringed spaces and you want to find the colimit, well you can guess that you have to do: Take the colimit of the $X_i$ and the limit of the $\mathcal{O}_{X_i}$ (pullbacked to the colimit).
"...the sheaf condition on a presheaf can be expressed as stating that the contravariant functor takes colimits to limits"
This is not correct. The reason is that the index category can be rather wild and colimits in preorders don't care about that. In detail: Let $U : J \to O(X)^{op}$ be a small diagram. Then the limit is just the union $V$ of $U_j$. Thus $F$ preserves this limit iff sections on $V$ are sections on the $U_j$ which are compatible with respect to the restriction morphisms given by $U$. If $J$ is discrete and $U$ maps everything to the same open subset $V$ of $X$, then the compatible sections are $F(V)^J$, which is bigger than $F(V)$.
"... I have a copy of MacLane's "Categories for the Working Mathematician," but whenever I pick it up, I can never seem to get through more than two or three pages (except in the introduction on foundations"
I think this book is still one of the best introductions into category theory. It can be hard to grasp all these abstract concepts and examples, but it gets easier as soon as you get input from other areas where category theoretic ideas are omnipresent. Your example about gluing morphisms illustrates this very well.
I'm not sure what would work for this individual, but I'd be tempted to turn this around, Jeopardy! style. That is, instead of being presented with a diagram and trying to compute its limit/colimit, take some construction and devise a diagram which naturally expresses the construction as a limit or colimit.
So for example, this might be too easy, but consider the construction $X/A$ where $A$ is a subspace of a topological space $X$. Is this naturally a limit or colimit? Well, it's a colimit, but of what? Again, this may be too easy since your friend is comfortable around pushouts. For extra credit: what is the sensible meaning of $X/\emptyset$?
Or, take the graph of a function like $y = x^2$. Can this be thought of as a limit or colimit? This time it's a limit, namely the equal-izer of two functions from $\mathbb{R}^2$ to $\mathbb{R}$. (There's a more general lesson to be learned here, that limits are generally loci of suitable equations.)
How about the localization $\mathbb{Z}[1/p]$ where we invert a prime? Perhaps a little harder, do the same for the localization $\mathbb{Z}_p$. Or (would this be too familiar?) how would you express the $p$-adics as a limit?
Or, come up with the condition that a presheaf over a space is a sheaf. This might be either too familiar or too abstract, however. It might be best to take more concrete examples like the ones above. These are all off the top of my head, though, and somewhat untested by me personally.
Best Answer
I can't help you much with high-dimensional topology - it's not my field, and I've not picked up the various tricks topologists use to get a grip on the subject - but when dealing with the geometry of high-dimensional (or infinite-dimensional) vector spaces such as $\mathbb R^n$, there are plenty of ways to conceptualise these spaces that do not require visualising more than three dimensions directly.
For instance, one can view a high-dimensional vector space as a state space for a system with many degrees of freedom. A megapixel image, for instance, is a point in a million-dimensional vector space; by varying the image, one can explore the space, and various subsets of this space correspond to various classes of images.
One can similarly interpret sound waves, a box of gases, an ecosystem, a voting population, a stream of digital data, trials of random variables, the results of a statistical survey, a probabilistic strategy in a two-player game, and many other concrete objects as states in a high-dimensional vector space, and various basic concepts such as convexity, distance, linearity, change of variables, orthogonality, or inner product can have very natural meanings in some of these models (though not in all).
It can take a bit of both theory and practice to merge one's intuition for these things with one's spatial intuition for vectors and vector spaces, but it can be done eventually (much as after one has enough exposure to measure theory, one can start merging one's intuition regarding cardinality, mass, length, volume, probability, cost, charge, and any number of other "real-life" measures).
For instance, the fact that most of the mass of a unit ball in high dimensions lurks near the boundary of the ball can be interpreted as a manifestation of the law of large numbers, using the interpretation of a high-dimensional vector space as the state space for a large number of trials of a random variable.
More generally, many facts about low-dimensional projections or slices of high-dimensional objects can be viewed from a probabilistic, statistical, or signal processing perspective.