I grew up in Auburn, so Lake Martin was a frequent. I haven't done any serious shooting around there, however. Colors should be best in late October-mid November. Water levels will most likely be quite low, so the places you can get to from the water will be limited.
I remember it being really pretty around the Alexander city bridge...