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)

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

[RonMcK]

<insert your code here>

Your code will then look like this:

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

Thanks,

[Mad_Kitty]

Sorry

[Bob Phillips]

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

[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.