Multiple Ranges - Same Worksheet

[Walter]

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!

 

[Dave Peterson]

First, I wouldn't change the way that excel's VBA creates this event.

I'd use:
Private Sub Worksheet_Change(ByVal Target As Range)

Target represents the cell(s) that you're changing--it doesn't have anything to
do with the name of the range that you want to look through.

Second, if you rearrange your select case statements, it may make it easier to
understand--and less chance that someone enters a value that isn't formatted the
way you like (for instance .97991234 in C2).

I'd use something like:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim iColor As Long

If Target.Cells.Count > 1 Then
Exit Sub 'single cell at a time
End If

If Not (Intersect(Target, Me.Range("C2:C26")) Is Nothing) Then
Select Case Target.Value
Case Is < 0.9: iColor = 3
Case Is < 0.96: iColor = 6
Case Is < 0.98: iColor = 53
Case Is < 1: iColor = 16
Case Is = 1: iColor = 44
Case Else: iColor = xlNone
End Select
Target.Interior.ColorIndex = iColor

ElseIf Not (Intersect(Target, Me.Range("D2:d26")) Is Nothing) Then
Select Case Target.Value
Case Is < 0.98: iColor = 3
Case Is < 0.9955: iColor = 16
Case Is < 0.998: iColor = 53
Case Is < 1: iColor = 16
Case Is = 1: iColor = 44
Case Else: iColor = xlNone
End Select
Target.Interior.ColorIndex = iColor
End If
End Sub

========
And since I'm not sure what you really meant with the range names, maybe this:
If Not (Intersect(Target, Me.Range("C2:C26")) Is Nothing) Then
should be replaced with:
If Not (Intersect(Target, Me.Range("BestDel")) Is Nothing) Then

(same thing for the elseif line, too.

Ps. Check those break points and the icolor values. I _think_ I got them where
you wanted them.

 

[Jim Rech]

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