I'm a bit biased, but I agree with some of the others and say Wade Hampton and Highlands CC.....both have really good golf and great scenery.
If you are looking solely for views and dramatics, then Highlands Cove would win hands down. The view from the 1st Hole and 3rd tee make it worth the look. The view from behind #8 at Trillium is nice too.
The weather can be really iffy up here in October, it can be great or awful depending on the year. After October 15, it is usually chilly and windy up here. The first frost usually comes sometime in mid September. The majority of the leafy trees will turn color soon thereafter. Believe it or not, we have a couple of trees changing color currently and they do it this time every year.
As a rule, the drier the summer, the better the foliage. This year has been the wettest in quite a while, even wetter than last year. Therefore, don't expect a great leaf season this year. The 2001 and 2002 "drought" years had awesome color.