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

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



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

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.

--
Regards,
Tom Ogilvy



"AliH" wrote in message
...
Thanks - where did you get the color numbers from? that was what I was
looking for?

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



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

Hi AliH,

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



---
Regards,
Norman



"AliH" wrote in message
...
Thanks - where did you get the color numbers from? that was what I was
looking for?

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



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

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

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



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

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
"AliH" wrote in message
...
| Thanks - where did you get the color numbers from? that was what I was
| looking for?
|
| "Norman Jones" wrote in message
| ...
| 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
|
|
|
| "AliH" wrote in message
| ...
| 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
|
|
|
|
|
|

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


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

--
Regards,
Tom Ogilvy


"Jim Rech" wrote in message
...
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
"AliH" wrote in message
...
| Thanks - where did you get the color numbers from? that was what I was
| looking for?
|
| "Norman Jones" wrote in message
| ...
| 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
|
|
|
| "AliH" wrote in message
| ...
| 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
|
|
|
|
|
|