Of course, if you really want to get somewhere VFR, the more clear the better.
However, my favorite weather to fly in just around home is just when the winds begin to calm and the rain has stopped, after the passing of a thunderstorm. The mists, clouds, and character in the sky is amazing to appreciate. Similarly, I enjoy flying in the mists of the crack of dawn....
Picking through the morning fog in the mountains of British Columbia (turning back was always an option...)
And, my wake in the fog, parallel to my runway....