I am writing an Excel macro that loops through an array of names of
equipment. First the user is prompted to enter a date (as a week beginning
date). Then for each name in the array it creates a sheet, renames the sheet
tab with the name of the equipment plus the date and then colors the tab.
This action is going to be performed once a week. I want all the sheets
created for one week to have the same tab color. The sheets created next
week would have a different tab color (because sheets for past weeks are kept
for historical information in the Excel file).

I can think of two options for choosing a different color each week.
Option #1 - I can have an array of colors that I loop through. But how
would I know which color was used last week? New sheets will be inserted at
the front of the file each week. Is there a way to refer to the first sheet
in the file when it is not Sheet(1)?
Option #2 - I can have the user specify a color for this week's sheets. I
know that I can have the user type the name of a color into an input box and
go from there. But does anyone know of another way to have the user pick the
color?

I'm leaning towards Option #1, but help with either option would be very
much appreciated!
Thank you, Judy