It usually increases the "value" of the car. The better looking (read: realistic) the weathering, the better. Mind you, I never sold any of my stuff and I don't think I would anyways. They are "good enough" for me, but may be "cheap" of "ugly" to someone else.
The biggest thing is to experiment on your weathering. Start out with some cars you won't mind trowing out if it doesn't work. Go slow, be patient and don't overdo it (like I do!!) You never know. You may have a talent for this.
Its not that complicated and there's only one secret: Patience.