Re: Sheet Tab Color, Excel 2000 & 2003

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hello,

Example one worked.

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.

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:

    '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.


Thank you for your help,
jfcby
.

Relevant Pages

  • Re: Puppy Mastiff wants to Nip at Faces
    ... in my first college textbook on structured programming. ... they did was loop through an array to show how you could easily access ...   arrays and loops because it makes for less work. ...
    (rec.pets.dogs.behavior)
  • Re: Use only cells with data in
    ... with a zero base? ... loop) against another array. ...   For n = LBoundTo UBound ...
    (microsoft.public.excel.programming)
  • Re: Use only cells with data in
    ... with a zero base? ... loop) against another array. ...   For n = LBoundTo UBound ...
    (microsoft.public.excel.programming)
  • Re: Puppy Mastiff wants to Nip at Faces
    ... in my first college textbook on structured programming. ... they did was loop through an array to show how you could easily access ...   and improve your crack pot licensing fee calculator. ...
    (rec.pets.dogs.behavior)
  • Re: Copying all whilst Range Forbidden active
    ... worksheet as I have found that using the standard Protect Cells stops ... So far we have called it a loop because we have assumed that you may ... only want to get rid of the formulae in specific cells. ...     Cells.Copy ...
    (microsoft.public.excel.programming)