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.
I like both. My favorite thing to do is follow up a heavier weight lifting rotation with a true endurance rotation such as Slim Series. I used SS after a P90X rotation back in 2007 and found it to be an excellent combination!
I go as heavy as I can, but for 10-12 reps!! Works best for me!! Sometimes I finish the muscle off with a moderate weight as many reps as I can until failure! (which is usually 20-25). And I always try to mix it up some.
I've recently realized that light to moderate weights work best for me, after trying to convince myself for years that I need to go heavier. Whenever I try to lift heavy, I get joint problems or connective tissue strains, and I also get insatiably hungry.
I absolutely loved STS Meso 1, but a week or two into Meso 2 I strained my shoulder, plus the constant hunger was kicking in. When I stick to light to moderate -- think Gym Styles, Meso 1 -- I feel better. It's been sort of freeing, this is something I've just concluded in the past few months. I'm doing more circuit workouts and endurance workouts, letting my shoulder heal and not feeling hungry all the time.
Btw this is something my mom has been telling me for years (we have similar body types) but I was in denial and just didn't want to believe her. She's almost 80, I'm in my 40's, and I still haven't learned that I should listen to my mother!
Moderate to heavier for me, I was on vacation for a week and used the gym everyday, which allowed me to go much heavier than I can at home, especially for legs, and I swore I was noticing a difference in my legs.
Off the top of my head, I'd like to do something like 2 day splits at the gym, splitting front vs. back, and still getting in 1-2 full body at home with a dvd. Muscle Max and STS TB are my faves right now. The other days left would be for cardio.
I am moderate height and have a slim build, and my body responds best to the light-to-moderate weight approach. I also just enjoy that style of lifting better, like in the 8-15 rep range (with the exception being that I can go higher with the leg reps). I have done three cycles of STS and each time I come to Meso 3 I have a total dread factor. I think every person's physiology is different and, while it is good to mix it up and get out of your comfort zone on occasion (which is why I push my way through Meso 3), in general you just need to go with whatever makes you personally feel the best.