print macro base don tab color

[Dean]

I have a macro that prints all worksheets. Now I'd like it to print all
sheets that have a light blue color on their tab only. Can you help me with
that IF statement?

Thanks much!
Dean

 

[Gary Keramidas]

maybe something like this

Sub test()
Dim i As Long
For i = 1 To Worksheets.Count
If Worksheets(i).Tab.ColorIndex = 37 Then 'change to correct color
number
' print routine here
End If
Next
End Sub

 

[Dean]

Well, I tried this but it does not do anything: Probably I am wrong in the
if then part. or your color # is not mine. It's in row #5 of what I assume
is the default palette - how do I find the color numbers?

Please help. I want it to merely print out all worksheets that have the
right tab color

Thanks, Gary




Sub PrintAllLightBlueWorksheets()
Dim i As Long
For i = 1 To Worksheets.Count
If Worksheets(i).Tab.ColorIndex = 37 Then
Worksheets(i).PrintOut
End If
Next i
End Sub

 

[Paul B]

Dean, try ColorIndex = 34 , you can get a list of colors with this

Sub ShowColors()
' The row number is the ColorIndex value.
Dim N As Long
ActiveWorkbook.Sheets.Add.Name = "Excel_Colors"

For N = 1 To 56
Cells(N, 1).Interior.Color = ThisWorkbook.Colors(N)
Next N
End Sub



--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Dean]

Did you/can you check my macro - the problem may not be the color (34 didn't
work either). I'm weak and I tried to adapt what Gary had, but I'm not good
at this! I'm particularly suspicious of the

"Worksheets(i).PrintOut" line

It didn't bomb out, just did nothing, which could mean wrong color or merely
ineffective macro!

Thanks!

 

[Tom Ogilvy]

Sub test()
Dim i As Long
For i = 1 To Worksheets.Count
msgbox i & ": " & Worksheets(i).Name & _
" Tab Color Index: " & Worksheets(i).Tab.ColorIndex
Next
End Sub

 

[Dean]

Thanks everyone. I guess my macro worked, but I just didn't have the right
color code, which was 20 it appears, and it may not have been in the 5th row
after all.

D

 

[Gary Keramidas]

run this in a new workbook and then save the workbook.

Sub Colors()
Dim ctr As Long

For ctr = 1 To 56
Cells(ctr, 1).Interior.ColorIndex = ctr
Cells(ctr, 2).Value = "'" & Right("000000" & _
Hex(ThisWorkbook.Colors(ctr)), 6)
Cells(ctr, 3).Value = ctr
Cells(ctr, 4).Value = Cells(ctr, 1).Interior.Color
Cells(ctr, 2).Font.Name = "Arial"
Cells(ctr, 2).HorizontalAlignment = xlRight
Cells(ctr, 3).HorizontalAlignment = xlCenter
Cells(ctr, 4).HorizontalAlignment = xlLeft
Next ctr

End Sub