A movie is a product, and the more polished it is, you know that lot of thought and effort has gone into it. And the custom designed titles are a part of this attention to detail. Recently, surprisingly, I have seen good movies, and quite a few with good title sequences.
Sherlock Holmes, has a brilliant one where the video shots freeze and morph into water-colored pencil-textured drawings. Up in the Air, has one of better aerial picturizations I've seen. Catch me if you can, another beautifully animated piece, while Fight Club explores the neuron structures. Spiderman movies have webby webby titles, quite nicely done. Zombieland wins the best slo-mo, while Hellboy 2 has the coolest gears-in-gears sequence I've seen till date, simply amazing.
On the bollywood side, I like Rang De Basanti, which has a punchy background score and Don(the remake) which has monochrome greens. Bollywood tends to use scenes and songs often in the titles, I guess to save reel time and to add that one extra song.
The ones which take the cake hands down are the Bond movies though. Style, personified.