print macro base don tab color

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dean - 22 Jan 2007 23:26 GMT

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

Reply to this Message

Gary Keramidas - 22 Jan 2007 23:29 GMT

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

Signature

Gary

>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

Reply to this Message

Dean - 23 Jan 2007 00:02 GMT

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

> maybe something like this
>
[quoted text clipped - 14 lines]
>> Thanks much!
>> Dean

Reply to this Message

Paul B - 23 Jan 2007 00:20 GMT

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

Signature

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

> 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
[quoted text clipped - 32 lines]
> >> Thanks much!
> >> Dean

Reply to this Message

Dean - 23 Jan 2007 00:48 GMT

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!

> Dean, try ColorIndex = 34 , you can get a list of colors with this
>
[quoted text clipped - 47 lines]
>> >> Thanks much!
>> >> Dean

Reply to this Message

Tom Ogilvy - 23 Jan 2007 01:06 GMT

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

Signature

Regards,
Tom Ogilvy

> 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
[quoted text clipped - 60 lines]
>>> >> Thanks much!
>>> >> Dean

Reply to this Message

Dean - 23 Jan 2007 02:39 GMT

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

> Sub test()
> Dim i As Long
[quoted text clipped - 69 lines]
>>>> >> Thanks much!
>>>> >> Dean

Reply to this Message

Gary Keramidas - 23 Jan 2007 03:06 GMT

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

Signature

Gary

> 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
[quoted text clipped - 70 lines]
>>>>> >> Thanks much!
>>>>> >> Dean

Reply to this Message