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

Judy,

Check the colorindex of the sheet tab

Sheets("Sheet1").Tab.ColorIndex

Mike

"Judy Ward" wrote:

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

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

"Judy Ward" wrote:

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

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" wrote in message
...
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

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

"Mike Fogleman" wrote:

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" wrote in message
...
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