Test for color of sheet tab?

  • Thread starter Thread starter Judy Ward
  • Start date Start date
J

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
 
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
 
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
 
Back
Top