The moment shades clicked for me was in a bedroom with one bare bulb hanging over a reading chair, throwing light straight down like an interrogation lamp. I put a plain drum shade on it that same afternoon and the whole corner turned into somewhere you'd actually want to sit. That's really all a shade is doing, softening the source so the room looks lived in instead of inspected.
On color, I've gone back and forth more than I'd like to admit, but the honest answer is that off white, cream or a soft linen shade works in almost any room because it lets light through evenly and doesn't fight with your walls or your art. A dark shade, charcoal or navy or anything opaque, does something different, it pulls light down and out in a narrow pool instead of glowing all over, which is great over a reading chair and terrible if you wanted the lamp to light the whole corner. I made that mistake with a bottle green shade I was sure would look rich and moody, and it just made the bedroom look like a hotel bar nobody was in. For sizing, the rule I actually use is that the shade should be roughly three quarters the height of the lamp base, and wide enough that it extends past the widest part of the base on both sides, otherwise you get that mushroom look where the shade perches on top like a hat that doesn't fit. Measure the base first, then shop for the shade, not the other way around, because a shade that looked fine in a showroom photo can swallow a narrow base whole or sit on a wide one like a lid that's too small. In a small room, a shade that's too wide will eat the side table and crowd whatever's next to it, a lamp and shade together should leave you room to still put down a coffee cup.
Where shades genuinely fall short is task lighting. If you need real light to read fine print or work at a desk, a fabric shade is diffusing exactly the light you need, and you're better off with an adjustable task lamp with a simple metal shade that throws light down hard rather than softening it. Pleated or fabric shades also don't love a bathroom or a kitchen, the steam and grease get into the fabric and you can't really clean it back out. And if you're renting and the fixture is already ugly, swapping the shade helps but it won't fix a genuinely bad lamp underneath, you're dressing it, not rescuing it. The one mistake to avoid is buying the shade before you've measured the base, because by the time you notice it doesn't fit, you've usually already thrown out the receipt.