We have stayed at Rocky Mtn National park three times in September. Usually we try for the third week when aspen color peaks although aspen will probably be good color during the forth week at lower elevations. Moraine Park CG will probably be the only CG open after the third week. You can usually get an update on the internet.
The 3rd & 4th weeks of September are usually during the elk mating season. At Moraine Park you can sit at your campsite and watch the elk drama below you. You need good bincoulars. The bugling will continue until after dark.
This is a great time of year, minimal crowds, etc. It will be cool at night and we've had light snow. There are no hook-ups at Moraine CG.