Color of worksheet Tab

[AliH]

Is it possible to check the colour of a worksheet tab through code? if yes
how?

I have red tabs and green tabs and want to create a summary sheet of only
the red sheets

Thanks

 

[Norman Jones]

Hi AliH,

Try:

'============================>>
Public Sub TesterAAA()

Dim i As Long

For i = 1 To ActiveWorkbook.Worksheets.Count
If Worksheets(i).Tab.ColorIndex = 3 Then 'Red
Sheets("YourSummarySheet").Cells(i + 1, "A").Value _
= Worksheets(i).Name
End If
Next i

End Sub
'<<============================

Change the sheet name to suit.

 

[Tom Ogilvy]

Sub showcolornumbers()
For i = 1 To 56
Cells(i, 1).Value = i
Cells(i, 2).Interior.ColorIndex = i
Next

End Sub


would show you the colors available in your workbook.

 

[Norman Jones]

Hi AliH,

See ColorIndex Property in VBA help for a list for the default color
palette.

 

[AliH]

Thanks - where did you get the color numbers from? that was what I was
looking for?

 

[Jim Rech]

I would just add to Tom and Norman's replies that the color assigned to a
Colorindex is customizable under Tools, Options, Color. So it's not
necessarily a fixed thing. I.e., 3 is not always red, it can be any color
assigned to it.

--
Jim
| Thanks - where did you get the color numbers from? that was what I was
| looking for?
|
| | > Hi AliH,
| >
| > Try:
| >
| > '============================>>
| > Public Sub TesterAAA()
| >
| > Dim i As Long
| >
| > For i = 1 To ActiveWorkbook.Worksheets.Count
| > If Worksheets(i).Tab.ColorIndex = 3 Then 'Red
| > Sheets("YourSummarySheet").Cells(i + 1, "A").Value _
| > = Worksheets(i).Name
| > End If
| > Next i
| >
| > End Sub
| > '<<============================
| >
| > Change the sheet name to suit.
| >
| > ---
| > Regards,
| > Norman
| >
| >
| >
| > | > > Is it possible to check the colour of a worksheet tab through code? if
| yes
| > > how?
| > >
| > > I have red tabs and green tabs and want to create a summary sheet of
| only
| > > the red sheets
| > >
| > > Thanks
| > >
| > >
| >
| >
|
|

 

[Tom Ogilvy]

To add to Jim's excellent information, David McRitchie has a detailed page
on this topic:


http://www.mvps.org/dmcritchie/excel/colors.htm