The beauty of tights is the variety that it comes in - take advantage of the choices to complement the rest of your outfit. With a lighter spring or summer color and fabric (like chiffon), pairing a matte black leg can tone it down and make it go out-worthy. If your ensemble is all black and you want to stir things up, try a colored tight for a change. At work, paired with a conservative pin stripe or black skirt, I like a textured pair, something with a cable pattern, or a striped pattern. It lends a more interesting aesthetic than a smooth black leg. If it's really cold, I like thick knit tights as opposed to the thinner nylon versions - sometimes I'll wear these under my jeans for warmth!
Due to the neutral palette of my closet, I mainly own darker hues, and haven't been brave enough to try a brighter choice. If I were to be more adventurous however, a cranberry/burgundy or a deep purple plum would be my top picks. Since I'm less likely to wear colored tights often, I'd get mine from a cheaper store, like H&M;, or Target.
A note: I am strictly a fan of tights only for going out, not leggings, not footless versions. Some people may be able to rock that look, but I'm quite conscious of my disproportionately large calves, and wearing a clingy pair of leggings that hit right in the middle would make people laugh. At me.
For the Cadillac of tights, Wolford is reputed to have the best, most opaque black tights. Nina Garcia of Project Runway fame mentioned it in her book. Of course at $50/pair, these things better come with free drinks or something. If on sale, these are justifiable, but otherwise, my Capezios and DKNY pairs will suit me just fine.