I have 6 named ranges which are columns of data on the same worksheet. When
the user enters a percentage in column C, 1 of the 5 colors appear denoting
status. I have the following Select Case statement that works well for
column C:

Private Sub Worksheet_Change(ByVal BESTDel As Range)
Dim icolor As Integer
If Not Intersect(BESTDel, Range("$C$2:$C$26")) Is Nothing Then
Select Case BESTDel
Case Is = 1#
icolor = 44
Case 0.98 To 0.9999
icolor = 16
Case 0.96 To 0.9799
icolor = 53
Case 0.9 To 0.9599
icolor = 6
Case Is < 0.9
icolor = 3
Case Else
icolor = 0
End Select
BESTDel.Interior.ColorIndex = icolor
End If
End Sub

Now I want to do the same thing for the other ranges of data in Column D, E,
F, and G. However, the Case statement is different for the range of values
for each color. My next named range for Column D is BESTQual. The inner
part for this range will be:

Case Is = 1#
icolor = 44
Case 0.9980 To 0.9999
icolor = 16
Case 0.9955 To 0.9979
icolor = 53
Case 0.98 To 0.9954
icolor = 6
Case Is < 0.98
icolor = 3
Case Else
icolor = 0

Each column will have different data values for the same colors but each
column is different criteria that is being looked at. The user inputs a
number and a color appears depending upon which Case statement applies. If
you need more info let me know. Thanks!

Check your first post.

Walter wrote:

I have 6 named ranges which are columns of data on the same worksheet. When
the user enters a percentage in column C, 1 of the 5 colors appear denoting
status. I have the following Select Case statement that works well for
column C:

Private Sub Worksheet_Change(ByVal BESTDel As Range)
Dim icolor As Integer
If Not Intersect(BESTDel, Range("$C$2:$C$26")) Is Nothing Then
Select Case BESTDel
Case Is = 1#
icolor = 44
Case 0.98 To 0.9999
icolor = 16
Case 0.96 To 0.9799
icolor = 53
Case 0.9 To 0.9599
icolor = 6
Case Is < 0.9
icolor = 3
Case Else
icolor = 0
End Select
BESTDel.Interior.ColorIndex = icolor
End If
End Sub

Now I want to do the same thing for the other ranges of data in Column D, E,
F, and G. However, the Case statement is different for the range of values
for each color. My next named range for Column D is BESTQual. The inner
part for this range will be:

Case Is = 1#
icolor = 44
Case 0.9980 To 0.9999
icolor = 16
Case 0.9955 To 0.9979
icolor = 53
Case 0.98 To 0.9954
icolor = 6
Case Is < 0.98
icolor = 3
Case Else
icolor = 0

Each column will have different data values for the same colors but each
column is different criteria that is being looked at. The user inputs a
number and a color appears depending upon which Case statement applies. If
you need more info let me know. Thanks!

--

Dave Peterson

I am not exactly sure what you want help with. I do have a question though.
Will there always be only 26 rows of data in column C, or should it expand to
hold or lower the selection if the data changes?

If you are in need of the Range for D, E, F, and G you should replace the two
C's in your range to be D, E, and so on. example Range("$D$2:$D$26")

Walter wrote:
I have 6 named ranges which are columns of data on the same worksheet. When
the user enters a percentage in column C, 1 of the 5 colors appear denoting
status. I have the following Select Case statement that works well for
column C:

Private Sub Worksheet_Change(ByVal BESTDel As Range)
Dim icolor As Integer
If Not Intersect(BESTDel, Range("$C$2:$C$26")) Is Nothing Then
Select Case BESTDel
Case Is = 1#
icolor = 44
Case 0.98 To 0.9999
icolor = 16
Case 0.96 To 0.9799
icolor = 53
Case 0.9 To 0.9599
icolor = 6
Case Is < 0.9
icolor = 3
Case Else
icolor = 0
End Select
BESTDel.Interior.ColorIndex = icolor
End If
End Sub

Now I want to do the same thing for the other ranges of data in Column D, E,
F, and G. However, the Case statement is different for the range of values
for each color. My next named range for Column D is BESTQual. The inner
part for this range will be:

Case Is = 1#
icolor = 44
Case 0.9980 To 0.9999
icolor = 16
Case 0.9955 To 0.9979
icolor = 53
Case 0.98 To 0.9954
icolor = 6
Case Is < 0.98
icolor = 3
Case Else
icolor = 0

Each column will have different data values for the same colors but each
column is different criteria that is being looked at. The user inputs a
number and a color appears depending upon which Case statement applies. If
you need more info let me know. Thanks!

--
Message posted via http://www.officekb.com