使用Excel VBA代码的条件Case语句 [英] Conditional Case statement using Excel VBA code

问题描述

我正在尝试编写条件条件语句，以在excel中的特定列中搜索特定的字符串，并在与该字符串匹配时更改单元格的背景色.

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 .

这是我当前的代码:

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

推荐答案

第 Exit For 行在第一次之后就停止了迭代.我想，这不是您想要的.或者，您必须将其写在案例陈述中.

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.

这篇关于使用Excel VBA代码的条件Case语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！