Sheet Tab Color, Excel 2000 & 2003

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jfcby - 24 Jan 2008 16:28 GMT

Hello,

I have a worksheet with 30 worksheets. I would like to color each tab
a different color for reference purposes. I can't figure out how to
wright a macro to select a sheet and then select a tab color.

This is my macro to select each worksheet. How can it be modified to
select each color also?

Sub WorkSheetsTabColor()
'Tab Color every other worksheet
'Define Variables
Dim i As Integer
'Sreen does not flash (Turn Screen Updating "OFF")
Application.ScreenUpdating = False
'Changes Tab Color every other worksheet 2-count
For i = 1 To Worksheets.Count
Worksheets(i).Tab.ColorIndex = 44 'Yellow
i = i + 1
Next i
'Turn Screen Updating "ON"
Application.ScreenUpdating = True
End Sub

Thank you for your help,
jfcby

Reply to this Message

Conan Kelly - 24 Jan 2008 16:42 GMT

jfcby,

Instead of setting the color index to 44 each time, you could set it to "i":

Sub WorkSheetsTabColor()
'Tab Color every other worksheet
'Define Variables
Dim i As Integer
'Sreen does not flash (Turn Screen Updating "OFF")
Application.ScreenUpdating = False
'Changes Tab Color every other worksheet 2-count
For i = 1 To Worksheets.Count
''''Worksheets(i).Tab.ColorIndex = 44 'Yellow
Worksheets(i).Tab.ColorIndex = i
i = i + 1
Next i
'Turn Screen Updating "ON"
Application.ScreenUpdating = True
End Sub

But they may not be a nice, smooth flowing order. If you want more of a
gradually changing order, you will have to determine what the ColorIndex
codes are for each color in the order you want them and then put those codes
in the order you want them into an integer array variable and then set the
Tab.ColorIntex to the integer array variable.

HTH,

Conan

> Hello,
>
[quoted text clipped - 22 lines]
> Thank you for your help,
> jfcby

Reply to this Message

jfcby - 24 Jan 2008 16:55 GMT

Hello,

Is there another way to modify the code to specifiy a certain color
for each page?

Thank you for your help.
jfcby

Reply to this Message

Conan Kelly - 24 Jan 2008 17:06 GMT

Jfcby,

What do you mean? Do you want the colors to be in a specific order? Do you
want the user to be prompted what color to each sheet?

Yes for both of those questions.

If you want the colors in a specific order, then you need to know the
ColorIndex nubmer of each color and hard code the ColorIndex of each color
(in the correct order) into an array variable. Then you could set the tab
color to an element of the array index in the loop.

If you want to prompt the user, a quick & dirty way is to use InputBox, but
the user will have to know the ColorIndex code for each color. A more
complicated way is designing your own dialog box that has all the colors
listed and each color's index code coded into it.

If you mean something else, please let me know.

Conan

> Hello,
>
[quoted text clipped - 3 lines]
> Thank you for your help.
> jfcby

Reply to this Message

jfcby - 24 Jan 2008 17:29 GMT

Hello,

On worksheet("config").range("I3:I58") I have 56 of the color numbers
listed. Since I need certain ones I can use either the cell reference
or I can hard code them into an array.

All I need is to specify a tab color for each worksheet when the macro
is run either in an array or cell rreference.

Thank you for your help,
jfcby

Reply to this Message

Conan Kelly - 24 Jan 2008 18:00 GMT

jfcby,

you could declare an array variable in your code like this:

Dim pvarColorIndex as Variant

Then you could assign your ColorIndex Values to it like this (before the
loop):

pvarColorIndex = Array(44, 33, 22, 8, 16, 56, ...)

Make sure that you enter the ColorIndex numbers in the order you want the
sheets colored.

Then inside your loop, you could assign the color like this:

Worksheets(i).Tab.ColorIndex = pvarColorIndex(i - 1)

I put the "i - 1" in there because arrays are usually 0 based...ie a 30
element array has elements numbered 0 to 29. I believe you can change that
with an "Option Base" (or something like that) statement in the General
Declarations. Look up "Using Arrays" in VBA Help for more info. I don't
recall if sheets are 0 based or 1 based. Expierment with it and see.

You might also be able to do something like this:

'Explicitly declare a 1-based 30 element integer array variable
Dim pintColorIndex(1 to 30) as Integer
pintColorIndex = Worksheets("config").Range("I3:I32")
'''or use the following line
'pintColorIndex = Worksheets("config").Range("I3:I32").Value

(haven't tested this code...don't know if you can assign values to an array
like this)

Then in your loop you can assign the color like this:

Worksheets(i).Tab.ColorIndex = pintColorIndex(i)

Notice there is just an "i" in the array instead of "i - 1" because I
explicitly declared it as 1-based, not 0 based.

HTH,

Conan

> Hello,
>
[quoted text clipped - 7 lines]
> Thank you for your help,
> jfcby

Reply to this Message

jfcby - 24 Jan 2008 18:43 GMT

Hello,

Example one worked.

> you could declare an array variable in your code like this:
>
[quoted text clipped - 17 lines]
> Declarations. Look up "Using Arrays" in VBA Help for more info. I don't
> recall if sheets are 0 based or 1 based. Expierment with it and see.

Example two did not work and this is my macro: How can it be modified
to work?

Sub WorkSheetsTabColor3()
'Worksheet Tab Color Assigned in Array
'Define Variables
Dim i As Integer
'Explicitly declare a 1-based 30 element integer array variable
Dim pintColorIndex(1 To 30) As Integer
'Sreen does not flash (Turn Screen Updating "OFF")
Application.ScreenUpdating = False
'Assignning array with Color Index Values
pintColorIndex = Worksheets("config").Range("I3:I32")
'''or use the following line
'pintColorIndex = Worksheets("config").Range("I3:I32").Value
'Changes Tab Color every other worksheet 2-count
For i = 1 To Worksheets.Count
'Assignning each array color to worksheet tab
Worksheets(i).Tab.ColorIndex = pintColorIndex(i)
'Continuing to next worksheet
Next i
'Turn Screen Updating "ON"
Application.ScreenUpdating = True
End Sub

> You might also be able to do something like this:
>
[quoted text clipped - 13 lines]
> Notice there is just an "i" in the array instead of "i - 1" because I
> explicitly declared it as 1-based, not 0 based.

Thank you for your help,
jfcby

Reply to this Message

Conan Kelly - 24 Jan 2008 19:19 GMT

jfcby,

Here is one way you could do it (read important notes after the code):

Sub WorkSheetsTabColor3()
'Worksheet Tab Color Assigned in Array

'Define Variables
Dim i As Integer
'Explicitly declare a 1-based 30 element integer array variable
Dim pintColorIndex(1 To 30) As Integer
Dim prngCell As Range
Dim prngRange As Range

'Sreen does not flash (Turn Screen Updating "OFF")
Application.ScreenUpdating = False

Set prngRange = Worksheets("config").Range("I3:I32")
i = 1

'Changes Tab Color every other worksheet 2-count
For Each prngCell In prngRange.Cells
'Assignning each array color to worksheet tab
Worksheets(i).Tab.ColorIndex = prngCell.Value
i = i + 1
'Continuing to next worksheet
Next prngCell

'Turn Screen Updating "ON"
Application.ScreenUpdating = True
End Sub

In this example, I added a Range variable and a Cell variable:

Dim prngCell As Range
Dim prngRange As Range

I then assigned I3:I32 to the range variable (also set i equal to 1):

Set prngRange = Worksheets("config").Range("I3:I32")
i = 1

Then I looped through each cell in the range, setting the "i'th" worksheet
tab color index to the value of the cell, and I incremented "i".

There could be issues with this though. If you have 30 sheets to change the
color of and the range you use has more than 30 cells, then you will get
errors. If the range you use has less than 30 cells, only the same number
of sheets will get their colors changed, leaving off the last few sheets.

I'll work through an alternate method, more similar to your original code.
I'll post it in a few mins.

HTH,

Conan

Hello,

Example one worked.

> You might also be able to do something like this:
>
[quoted text clipped - 14 lines]
> Notice there is just an "i" in the array instead of "i - 1" because I
> explicitly declared it as 1-based, not 0 based.

Thank you for your help,
jfcby

Reply to this Message

Conan Kelly - 24 Jan 2008 19:32 GMT

jfcby,

Now for this one:

Sub WorkSheetsTabColor3()
'Worksheet Tab Color Assigned in Array
'Define Variables
Dim i As Integer
'Explicitly declare a 1-based 30 element integer array variable
Dim pintColorIndex(1 To 30) As Integer
Dim prngCell As Range
Dim prngRange As Range

'Initialize variables
Set prngRange = Worksheets("config").Range("I3:I32")
i = 1

'Assignning array with Color Index Values
For Each prngCell In prngRange.Cells
pintColorIndex(i) = prngCell.Value
i = i + 1
Next prngCell

'Sreen does not flash (Turn Screen Updating "OFF")
Application.ScreenUpdating = False

For i = 1 To Worksheets.Count
'Assignning each array color to worksheet tab
Worksheets(i).Tab.ColorIndex = pintColorIndex(i)
'Continuing to next worksheet
Next i

'Turn Screen Updating "ON"
Application.ScreenUpdating = True
End Sub

I haven't tested this...don't know if it will work.

With this one, I left your loop alone, but I added a loop before that to
assign the ColorIndex values to each element of the array variable.

There are still possiblity for errors here: if the number of cells does not
equal the number of elements in the array variable, or the number of sheets
does not equal the number of elements in the array.

This code needs some work to be completely error free (or to handle errors)
if the number of cells or number of sheets change from file to file.

HTH,

Conan

Hello,

Example one worked.

Thank you for your help,
jfcby

Reply to this Message

jfcby - 24 Jan 2008 20:53 GMT

Hello Conan Kelly,

Thank you for help, The codes work great and learned how to use
arrays!

jfcby

Reply to this Message

Conan Kelly - 24 Jan 2008 21:20 GMT

Glad I could help.

> Hello Conan Kelly,
>
> Thank you for help, The codes work great and learned how to use
> arrays!
>
> jfcby

Reply to this Message