It very much depends on what you want to achieve. Let’s take for example someone who is looking for fat loss and overall strength improvement. This person’s programme should start with a slightly higher repetition range to enable to muscles the build a little endurance into them. In this phase look at completing around 15 repetitions and, on some exercises, push up to 20.
The next phase would take the client into a 'building muscle' phase which brings the repetition range down to 8-12 reps per set. The weight should feel heavier now. The last few reps on each set should now be feeling manageable, but challenging.
The next phase will allow the reps to be lower 5-8 per set meaning the weight will be much heavier now. This would be a strength phase. This type of cycle would normally be around 12-16 weeks depending on how many times per week the client will train, and their general progression.
In general, I would always say lift the heaviest weight you can for the desired amount of repetitions. Without challenging weights the muscles won’t develop and grow. This is the same for men and women. If the desired outcome is to lose body fat then there has to be weights involved!