I will go slightly out of my way if they look fun or if they are something I can do to supplement my playing without going to far away from it. Like the one from Red Dead where you put all hog tied woman on the train tracks, that was awesome. The one for doing all the ambiant challenges was really fun too especially the hunting part. Sometimes it adds something that you didn't think of doing before, like taking out a pack of wolves with your knife, I wouldn't have even tried that had it not been tied to an achievement, but it was fun so I gave it a go.
I don't like online achievements though, they just feel like a grind or a cheat or a way to keep you constantly playing the game, Gears 2 Seriously 2.0 anyone? So not worth it.
Games with achievements that I really liked, Fallout 3, Mass Effect 2, Assassins Creed 2, I have nearly all the achievements in those games as a result.