Test for color of sheet tab?

[Judy Ward]

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

 

[Mike H]

Judy,

Check the colorindex of the sheet tab

Sheets("Sheet1").Tab.ColorIndex

Mike

 

[Mike H]

Judy,

I missed part of your question. Sheet(1) is always the first sheet
irrespective of it's name so

MsgBox Sheets(1).Tab.ColorIndex

will return the tab colour of the leftmost sheet

Mike

 

[Mike Fogleman]

I would pre-assign the colors for each week and either hard code them in VBA
or list them in a hidden sheet for reference. From the user input of the
date, get the WeekNum and use that number to select the color you assigned.

Mike F

 

[Judy Ward]

Thank you, both Mike H and Mike F, for responding.

When I am editing a macro I can see the sheet names and that the first sheet
I visually see in the Excel file is "Sheet1344(Equipment Name #01 6-9)". I
didn't realize this sheet is considered Sheet(1) in code--that helps.

I'm intrigued by the idea of assigning tab colors based on week numbers.
Thank you very much for the suggestion.

Thanks again,
Judy