Array problem

[scrappe7]

Could anyone help with the following, you may have helped with this code before

Sub NumColors()
Dim x As Integer
Dim i As Integer
Dim ncell As Range
Dim vColors As Variant
vColors = Array(3, 4, 6, 7, 8, 10, 12)
'Cells to enter dates are labeled as a range "NUM"
Range("Num&quot.Select 'Cells to enter dates are labeled as a range "NUM"
For Each ncell In Selection
If ncell.Value <> &quot;&quot; Then ' If cell is blank then it gets skipped
ncell.Interior.ColorIndex = vColors(ncell.Value)
Else
ncell.Interior.ColorIndex = xlNone
End If
Next ncell
End Sub

This line is causing the problem
ncell.Interior.ColorIndex = vColors(ncell.Value)

I get an out of range error if left that way
If i take out the (ncell.value) it colors everything in as 3(red). If i have 100 different values i need them have different colors based on the array? how can i make 100 different ncell values have at least 30-40 different colors based on what i enter in the array??
Thanks for the help.

Ed

 

[vbMax]

Does the value of the cell contain the desired color
(3 = red, etc.)? If so, change the line:
ncell.Interior.ColorIndex = vColors(ncell.Value)
to:
ncell.Interior.ColorIndex = ncell.Value


If the value of the cell contains the POSITION of the
color in your array, you must do two things. First,
enter the following line above your &quot;Sub NumColors()&quot;
line:
Option Base 1
This line will cause the array subscripts to start at
1 and not as 0. For example, the first element of the
array will now be referred to as vColors(1) and not
vColors(0). Also, the value of the cell cannot exceed
the number of elements in the array. For example, if
your array contains 7 elements, the value of any of
your cells cannot exceed 7 or you will get the &quot;out of
range&quot; error.