The following should help-

You should assign names to each of the color ranges rather than using cell
addresses so you can insert rows, etc. and not have rewrite your code. I'm
assuming you have ranges named "Rg1", "Rg2", etc

Also you have to code for users changing more than one cell at a time, like
by using Ctrl-Enter. Each cell could be in a different range...

Private Sub Worksheet_Change(ByVal Target As Range) ''Don't change the std
header
Dim iColor As Integer
Dim Cell As Range
For Each Cell In Target
If Not Intersect(Cell, Range("Rg1")) Is Nothing Then
Select Case Cell.Value
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
Cell.Interior.ColorIndex = iColor
ElseIf Not Intersect(Cell, Range("Rg2")) Is Nothing Then
Select Case Cell.Value
''add new table...
Case Is = 1

Case Else
iColor = 0
End Select
Cell.Interior.ColorIndex = iColor
ElseIf Not Intersect(Cell, Range("Rg3")) Is Nothing Then
''....
End If
Next
End Sub



--
Jim
"Walter" wrote in message
...
|I am new to VBA. I have 6 named ranges which are columns of data. 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!