CODEWITHSUNDEEP
vbaexcelconditional-formatting
BuellerM123
BuellerM123

Reputation: 1

Conditional Case statement using Excel VBA code

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

Upvotes: 0

Views: 1136

Answers (2)

Mathieu Guindon
Mathieu Guindon

Reputation: 71247

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.

Upvotes: 0

FishermansFriend
FishermansFriend

Reputation: 52

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.

Upvotes: 2

Related Questions