Select Case Loop

Question

I modeled a Select Case loop based on another question I found on here(Link: Excel VBA Select Case Loop Sub).

The changes I had to do to make it applicable were pretty minimal, and I don't see where I went wrong. Here's my code:

Private Function getColor(ByVal MatVal As Range) As Integer
        Select Case MatVal
            Case 0 To 1
                getColor = 9: Exit Function
            Case 1.01 To 3
                getColor = 46: Exit Function
            Case 3.01 To 5
                getColor = 27: Exit Function
            Case 5.01 To 10
                getColor = 4: Exit Function
            Case 10.01 To 20
                getColor = 5: Exit Function
            Case 20.01 To 30
                getColor = 11: Exit Function
            Case 30 To 100
                getColor = 29: Exit Function
        End Select
End Function

Which is called via:

Set LipR = Workbooks("LMacro.xlsm")
Set SecX = Application.Workbooks.Open(Path & "SecX.csv")
Set Xws = SecX.Sheets("SecX")


Set Lws = LipR.Sheets("Funds")




    With Lws
        For i = 2 To 10 'LwsRows



            If Lws.Range("A" & i).Value <> "" Then
            LipR.Sheets.Add(After:=LipR.Sheets(LipR.Sheets.Count)).Name = Lws.Range("A" & i).Value
            NewFund = Lws.Range("A" & i).Value
            Set Fsheet = LipR.Sheets(NewFund)               


            End If

                With Fsheet
                    FsheetRows = .Range("A" & .Rows.Count).End(xlUp).Row
                End With

                ....                    
                Set MatPhase = Fsheet.Range("O4:O" & FsheetRows)

                For Each MatVal In MatPhase.Cells
                    MatVal.Interior.ColorIndex = getColor(MatVal)
                Next MatVal

                Fsheet.Cells.EntireColumn.AutoFit

                Application.Goto _
                Reference:=Fsheet.Range("A1"), Scroll:=True

        Next i
    End With

What am i missing here? I was really trying to avoid an if/elseif for this.

Thank you

**1.** What's the problem? **2.** You don't need "Exit Function" after every line; once one of the cases evaluates to true, `getColor` will be set, and then it will exit the case expression because no other cases will evaluate to true. — LittleBobbyTables - Au Revoir, Jul 23 '13 at 17:35
I just tested this and it worked fine. I did change the `MatPhase` range to be this: `Set MatPhase = ActiveSheet.Range("A1:A10")` . Maybe check that `Fsheet` and `FsheetRows` are legitimate values? — rwisch45, Jul 23 '13 at 17:42
When I run it, the colors are all over the place, or non existant. The cells in MatPhase are formula driven, if that makes a difference. I added a matphase.select followed by a stop right after it's set, and it is getting the right cells. — Swolerosis, Jul 23 '13 at 17:56
You'll get non-existent colors for any cells in the MatPhase range that have values below 0 or > 100. What do you mean by the colors being "all over the place"? — Jon Crowell, Jul 23 '13 at 19:03
This loop should be called for every sheet created, so i'll give a couple examples. On the first sheet created i have -113 a few times, and 17.52. Neither is Colored. I expected this on the -113, and want it white, but not the 17. The next sheet I have values ranging from .09 to 30, all highlighted green. A few sheets later the range is all blue. I think you're right in that the problem is in my Sub, so I'll edit my post and add more of the surrounding code and see if that helps. — Swolerosis, Jul 23 '13 at 19:21

Jon Crowell · Answer 1 · 2013-07-23T19:16:56.007

My guess is that you went wrong in the sub that calls the function. The following works without making any modifications to your function, as long as you have values in the range you are looping through that fall between 0 and 100.

In my example, I'm using Sheet2 and setting FsheetRows to 20. I included a screenshot with the a copy of the values from column O in column P so in case the cell colors make it hard to see. Modify as needed to use the objects in your workbook.

Sub ColorMyCells()

    Dim Fsheet As Worksheet
    Dim FsheetRows As Long

    Set Fsheet = ThisWorkbook.Sheets("Sheet2")
    FsheetRows = 20

    Dim MatVal As Range, MatPhase As Range

    Set MatPhase = Fsheet.Range("O4:O" & FsheetRows)

    For Each MatVal In MatPhase.Cells
        MatVal.Interior.ColorIndex = getColor(MatVal)
    Next MatVal
End Sub

After running this, the range now looks like column O in the screen shot below:

Set cell color with select statement

This works for me when I create it in a new book as well. I've tried tie it to mine, and make sure the variables are coming through correctly. When I changed teh code to select MatPhase, it selected the right cells. I had it output the value for Fsheetrows, and it did so on the corrosponding Fsheet. All of my variables declared to the same type as well. — Swolerosis, Jul 23 '13 at 19:46

score 0 · Answer 2 · answered Jul 23 '13 at 17:47

0

I guess your MatVal is a single cell/range

Private Function getColor(ByVal MatVal As Range) As Integer

    Select Case MatVal.Value
        Case 0 To 1: getColor = 9
        Case 1.01 To 3: getColor = 46
        Case 3.01 To 5: getColor = 27
        Case 5.01 To 10: getColor = 4
        Case 10.01 To 20: getColor = 5
        Case 20.01 To 30: getColor = 11
        Case 30 To 100: getColor = 29
    End Select
End Function

answered Jul 23 '13 at 17:47

matzone

5,703
3
17
20

MatVal should represent a single cell each time it loops, but I want it to work it's way down the range MatPhase, coloring each cell accordingly. Fsheet is also dynamic, and part of a bigger loop that creates new tabs based on other values. This For Each loop is just one formatting piece I need done on each sheet as it loops through. I have another color formatting setup for only red in another column using an if then statement and it works fine, but with the multiple classes I thought a Select Case would be better. – Swolerosis Jul 23 '13 at 18:17

Select Case Loop

2 Answers2