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
– LittleBobbyTables - Au Revoir, Commented Jul 23, 2013 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
– rwisch45, Commented Jul 23, 2013 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
– Swolerosis, Commented Jul 23, 2013 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
– Jon Crowell, Commented Jul 23, 2013 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
– Swolerosis, Commented Jul 23, 2013 at 19:21

Jon Crowell · Accepted Answer · 2013-07-23 19:16:56Z

1

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

edited Jul 23, 2013 at 19:16

answered Jul 23, 2013 at 17:43

Jon Crowell

22.4k15 gold badges94 silver badges113 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Swolerosis Over a year ago

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.

matzone · Accepted Answer · 2013-07-23 17:47:43Z

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, 2013 at 17:47

matzone

5,7293 gold badges19 silver badges20 bronze badges

1 Comment

Swolerosis Over a year ago

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.

Collectives™ on Stack Overflow

Select Case Loop

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related