As many others have said I'd look at South Norwalk or Downtown Stamford. I'd avoid Bridgeport personally, although it may be more affordable.

As for the weather, it's beautiful in the spring and summer. The fall can be nice, but yeah, the winters suck. It's cold and dark. But people from New England/the Northeast just kind of deal with that. There are fun seasonal things in winter to brighten your mood, but I definitely feel you on the seasonal depression part.