Well, I was going to use my intro as my first post, but this one just had to be jumped on. lol
With colored filters, it's almost best to think of light in terms of opposites to help in understanding which way to move on the spectrum when choosing a filter to do any color correction while shooting....and as an example, it's easiest (at first [at least to me]) to think in terms of black and white film:
Let's say you're shooting a picture (BW) of a model. Fairly simple shot, but you want to make the red lipstick on the model stand out more. In BW photography, to do this, you have to imagine what will happen depending on what color filter you choose. Even though the color of the filter will have no effect on the actual "color" of the BW image, the values WILL be affected.
Let's say you use no filter whatsoever for this shot....you'll probably get a moderate grayscale image. But, let's say you use a red filter. Well, since the lipstick is red and the filter filters out red, you'd end up with what appears to be a set of white lips. Okay, well that's not the effect we were after, because now your model looks like a corpse. We want to make the lipstick stand out more.....so what's on the opposite end of the color spectrum from red? GREEN. The green filter will make the red lipstick appear almost black.
Okay, so how does this relate when using color film? Well, you have to imagine what happens with color film when you're in certain types of lighting, just as ksmattfish mentioned.
If you're using regular color film, it's typically balanced for daylight color. When you go inside, where most of us have regular incandescent bulbs (household lightbulbs), things suddenly take on a much warmer appearance...the color is being shifted to the redder side of the spectrum. Actually appearing mostly kind of an orange tone. To fix this, you have to move back towards the cooler side of the spectrum. So to balance the color back from red, you have to use a lightly green-tinted filter to shift it all back to the zero point (daylight color).
Now, what if you're in florescent lighting? Well assuming they're not daylight balanced, this will give a greenish tint to your pictures (to me this gives the image a sickly feel). So this time, to balance from the green color of the florescent lights, you'll have to use a warming filter that leans back towards the red side of the spectrum.
With tungsten lights (which are common in high school basketball gymnasiums), the image becomes very orange. This would require a tungsten filter, which takes on a bluish tint.
And also typical in gyms are mercury vapor lights, which have a tendency to make your images somewhat blue, I believe. I can't remember fully on this one, but I think you would just follow the reversed step you would take for tungsten to fix this one...which would be a slightly orangish filter.