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