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
– Nathan_Sav, Commented May 24, 2017 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. — user4039065
– user4039065, Commented May 24, 2017 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
– Mathieu Guindon, Commented May 24, 2017 at 16:08

FishermansFriend · Accepted Answer · 2017-05-24 15:50:04Z

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, 2017 at 15:50

FishermansFriend

529 bronze badges

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

Comments

Mathieu Guindon · Accepted Answer · 2017-05-24 16:16:41Z

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.

Collectives™ on Stack Overflow

Conditional Case statement using Excel VBA code

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related