I use both what I consider "moderate" weights (failure at around 15 reps for upper body and 25ish for lower body) and "heavy" weights (failure at around 8-10 reps for upper, 12-15 for lower).
For strength, the 'heavy' weights work best, but for visible results, 'moderate' weights work best for me. I did an entire P90x rotation with most exercises at 15 reps.
I occasionally do lighter weight workouts, to change things up, but they are either quite light (3-5#) (and more '4-limbed aerobics' type workouts, like the Firm, or high-rep workouts like Jari Love (still reaching failure, but at a much higher rep range).
I do think it's good to mix things up, though.
Since I'm doing a lot of 5ks (as a speedwalker) in spring-fall, I do more endurance-based weight work, and stick with light-to-moderate weights. In the winter, I'll do more heavy weights, to build a strength base without having it interfere with my training.