Why does my rear derailleur's indexing work for either the high or the low gears, but not both?
When adjusting the tightness of the derailleur cable, it should affect the shifting across the whole range of gears evenly. The way you phrased the question, it sounds like you are experiencing problems mainly in the high end when it's too loose and mainly in the low end when it's too tight. That would indicate that your derailleur hanger is out of alignment. You can check that by looking at your bike from behind and seeing if the two small pulley wheels on your derailleur are directly in line with the gears, and not bent at a slightly different angle. It's an easy fix but it requires a specific tool. 

On the other hand, and as others have suggested, it is very common for grime to build up around the bend in the rear shift cable. It may be hesitant to shift into the highest gear because the remaining tension in the derailleur's spring (which will be at its lowest when the derailleur is most relaxed, or in the highest gear) is not enough to overcome the residual friction between the cable and housing. Sometimes you can solve it just by cleaning the cable. To check/clean it without any tools: 


shift into the highest (most outward) gear in the rear
Hold the rear wheel off the ground (somehow)
Pedaling forward with your right hand, use your left hand to push the derailleur in (shift into the lower/inward gears). This will create slack in the shift cable. 
Take out the housing coming from the rear derailleur from the frame stub holding it in. You can do this because the shift cable should not be under tension. 
Slide the section of housing out of the way and wipe the cable down thoroughly with a cloth. If you have any, apply a drop of chain oil to the cable (oil your chain while you're at it).
Reposition the housing admire your newly reduced cable friction