You can switch them out whenever you want. You can only have one in at a time! They also affect the color of the water, which means you may want to download multiple to use in different worlds (Isla Paradiso water would look odd in Twinbrook!). Lighting mods replace the lighting in every world. This is a part of my Comprehensive Guide to The Sims 3: Performance and Custom Content for FAQ: 'I don't like the look of the game/the sims.' The majority of these can affect performance of the game! If your computer is already struggling to run The Sims 3, even after trying many suggestions, I would only recommend non-default skin and eyes for your sims and maybe a lighting mod and a few custom sliders. With many of the recommendations in this post, this is what my game looks like, and I'm happy with it. I personally think The Sims 3 has a lot of potential.
But if you fundamentally dislike the style, there's only so much that can be changed. There are many mods that can improve the appearance of sims and the environment. The graphics of The Sims 3 now look dated to some - even when it was new many people disliked the style.
