Conditional Case statement using Excel VBA code

Question

I am trying to write a conditional case statement that searches through a specific column, in excel, for a specific string and when it matches with the string that cell's background color is changed.

If the cell is empty or does not match the string then nothing should happen to the cell.

Right now I am trying to iterate through each cell in the column and check all possible string values to compare to but it does not seem to be working .

Here is my current code:

Sub interiorsStatus()

Dim sh As Worksheet
Dim rw As Range


Set sh = ActiveSheet

For Each rw In sh.Rows
Select Case sh.Cells(rw.Row, "E").Value

    Case "DELIVERED"
        result = Range(rw.Row).Interior.ColorIndex = 33

    Case "READY TO ORDER"
        result = Range(rw.Row).Interior.ColorIndex = 36

    Case "ORDERED"
        result = Range(rw.Row).Interior.ColorIndex = 39

    Case "DELIVERED"
        result = Range(rw.Row).Interior.ColorIndex = 43

    Case "EXISTING"
        result = Range(rw.Row).Interior.ColorIndex = 40

    Case "ON HOLD"
        result = Range(rw.Row).Interior.ColorIndex = 48

    Case "GENERAL CONTRACTOR"
        result = Range(rw.Row).Interior.ColorIndex = 2

    Case "AV & BLINDS"
        result = Range(rw.Row).Interior.ColorIndex = 15

    Case "MILLWORK"
        result = Range(rw.Row).Interior.ColorIndex = 22

    Case Else
        result = """"

     End Select

     Exit For

      Next rw

    End Sub

`rw.Interior.ColorIndex = 22` will do and `select case rw.cells(1,5)` — Nathan_Sav, May 24 '17 at 15:47
`For Each rw In sh.Rows` should be `For Each rw In sh.USEDRANGE.Rows` or you are going to examine 1,048,576 rows. There are other alternatives to finding the last non-blank row. — , May 24 '17 at 15:57
Is `result` declared? Or used anywhere? Because right now it seems to be just an undeclared local being assigned and then promptly discarded. Also, yeah, `Exit For` just before `Next` ...is going to force-choke the loop after the first iteration. — Mathieu Guindon, May 24 '17 at 16:08

score 2 · Answer 1 · answered May 24 '17 at 15:50

2

The line Exit For stops your iteration after the first time. I think, this is not, what you want. Or you have to write it inside the case-statement.

answered May 24 '17 at 15:50

FishermansFriend

52
9

score 0 · Answer 2 · answered May 24 '17 at 16:16

result = Range(rw.Row).Interior.ColorIndex = 40

That's an assignment. It's assigning to result, the value of the expression to the right of the assignment operator.

Range(rw.Row).Interior.ColorIndex = 40

When you have that on the right-hand side of an assignment operator, that's a Boolean expression, it evaluates to True or False. So result will be True when ColorIndex is 40, and False otherwise.

And then nothing gets done with the result.

If you intended to actually set the ColorIndex, remove the result = assignment to turn the Boolean expression into an assignment instruction that assigns ColorIndex.

And then there's the other problem: you explicitly exit the loop just before you finish the first iteration. Remove that Exit For if you want to actually loop.

Conditional Case statement using Excel VBA code

2 Answers2

Linked