在VBA中使用通配符用于条件格式(Excel 2003)

I worked up the following VBA to add a bunch of conditional formatting to a worksheet in Excel 2003 (which only allows 3 conditions otherwise), but it won't function correctly with wildcards. If I substitute the wildcards for exact values, it runs correctly. I suspect the last statement about <> may require adjusting, but I don't know how to jive the LIKE "Wildcard" with <> at the end. VBA code below:

我编写了下面的VBA来向Excel 2003中的工作表添加一些条件格式(否则只允许3个条件)，但是它不能正确地使用通配符。如果我用通配符替换精确的值，它就会正确运行。我怀疑关于<>的最后一个声明可能需要调整，但是我不知道如何将类似的“通配符”与<>混合在一起。VBA代码如下:

    Private Sub Worksheet_Change (ByVal Target As Range)
    Set MyPlage = Range(“C3:I11,C13:I34”)
        For Each Cell in MyPlage

    If Cell.Value Like “A*” Then
            Cell.Interior.ColorIndex = 38
    End If
    If Cell.Value Like “B*” Then
            Cell.Interior.ColorIndex = 35
    End If
    If Cell.Value Like “C*” Then
            Cell.Interior.ColorIndex = 34
    End If
    If Cell.Value Like “D*” Then
            Cell.Interior.ColorIndex = 40
    End If
    If Cell.Value <> “A*” And Cell.Value <> “B*” And Cell.Value <> “C*” And Cell.Value <> “D*” Then
    Cell.Interior.ColorIndex = xlNone
    End If

    Next
End Sub

1 个解决方案

#1

You don't really need wildcards since it's just a basic If...ElseIf...End If structure:

你不需要通配符，因为它只是一个基本的If…如果结构:

   Private Sub Worksheet_Change (ByVal Target As Range)
    Set MyPlage = Range(“C3:I11,C13:I34”)
        For Each Cell in MyPlage

    If Cell.Value Like “A*” Then
            Cell.Interior.ColorIndex = 38
    ElseIf Cell.Value Like “B*” Then
            Cell.Interior.ColorIndex = 35
    ElseIf Cell.Value Like “C*” Then
            Cell.Interior.ColorIndex = 34
    ElseIf Cell.Value Like “D*” Then
            Cell.Interior.ColorIndex = 40
    Else
        Cell.Interior.ColorIndex = xlNone
    End If

    Next
End Sub