VBA multiple ranges

[Mad_Kitty]

A while ago xld helped me out and created the code below, I modified it to include a named range which I called Info, now I need it to only work on certain cells so have created three discontinuous named ranges Info07, Info08 and Info09 but somehow need to link them togeather and get the code to work on them.

I think I should be using Union but not sure how to make it work with the code.

Any help gratefully recieved even if only being pointed to another website.

Thanks (from long time lurker)



[VBA]Private Sub Worksheet_Calculate()
Dim Target As Range
Dim icolor As Long
On Error Resume Next
Application.DisplayAlerts = False
For Each Target In Me.Range("E17:P51")
icolor = xlColorIndexNone
Select Case Target
Case 0 To 0: [White]
Case 1 To 75: icolor = 3
Case 76 To 95: icolor = 26
Case 96 To 100: icolor = 45
Case 101 To 102: icolor = 46
Case 103 To 105: icolor = 4
Case 106 To 1000: icolor = 50
Case Else: 'Whatever
End Select

Target.Interior.ColorIndex = icolor
Next Target
End Sub


[/VBA]

[RonMcK]

[uvba][vba]<insert your code here>[/vba][/uvba]

Your code will then look like this:[vba]Private Sub Worksheet_Calculate()
Dim Target As Range
Dim icolor As Long

On Error Resume Next
Application.DisplayAlerts = False

For Each Target In Me.Range("E17:P51")

icolor = xlColorIndexNone

Select Case Target
Case 0 To 0: [White]
Case 1 To 75: icolor = 3
Case 76 To 95: icolor = 26
Case 96 To 100: icolor = 45
Case 101 To 102: icolor = 46
Case 103 To 105: icolor = 4
Case 106 To 1000: icolor = 50
Case Else: 'Whatever
End Select

Target.Interior.ColorIndex = icolor

Next Target

End Sub
[/vba]

Thanks,

[Mad_Kitty]

Sorry

[Bob Phillips]

[vba]

Private Sub Worksheet_Calculate()
Dim Target As Range
Dim icolor As Long
On Error Resume Next
Application.DisplayAlerts = False
For Each Target In Union(Me.Range("Info07"), Me.Range("Info08"), Me.Range("Info09"))

icolor = xlColorIndexNone
Select Case Target

Case 0 To 0: [White]
Case 1 To 75: icolor = 3
Case 76 To 95: icolor = 26
Case 96 To 100: icolor = 45
Case 101 To 102: icolor = 46
Case 103 To 105: icolor = 4
Case 106 To 1000: icolor = 50
Case Else: 'Whatever
End Select

Target.Interior.ColorIndex = icolor
Next Target
End Sub
[/vba]

[RonMcK]

Mad_Kitty,

Don't worry about it. This is one of those things we each learn as we begin using the website. Your code is short an sweet; the challenge is when someone posts 2 or 3 screensful of code without using the VBA tags. That much code, all flush left, quickly dampens others' enthusiam for helping the original poster (aka OP).

Cheers,

[Mad_Kitty]

One thing I have picked up on is any longer and you upload example so people know what your talking about.

[RonMcK]

Ah, yes. Around here everything goes better with ... a sample workbook. (borrowing from someone's advertising slogan)


Thanks,

[RonMcK]

XLD,

I assume that one could have specified the subranges in an absolute format. The advantage to named ranges are that one's code is less cluttered, and the named ranges will automatically expand (or shrink) whenever a named range is affected by row and/or column inserts and deletions?

Thanks,

[Bob Phillips]

Yes, and the OP did mention named ranges.

Also, names are part of Excel, so it is clearer to most people that they need changing, whereas hard-coding may not.