如何在For Each循环中为Excel单元格着色？VBA代码排障

代码中的核心问题 #

• 区域范围错误：原代码中Range("L2" & last_row)会生成错误的单元格地址（例如last_row=10时，会变成L210而非L2:L10），导致遍历范围完全错误。
• 未声明变量：多个变量未显式声明，容易引发逻辑混乱，建议添加Option Explicit强制变量声明。
• Filter函数逻辑错误：当单元格值不在目标数组中时，Filter返回空数组，此时访问vFilter(i)会触发下标越界（或无意义的对比），判断逻辑完全失效。

修正后的代码 #

Option Explicit

Sub data_validation_from_array()
    Dim packages As Variant
    Dim active_sheet As Worksheet
    Dim last_row As Long
    Dim rng As Range
    Dim cel As Range
    Dim cell_value As String
    
    Set active_sheet = ActiveSheet
    last_row = active_sheet.Range("L" & active_sheet.Rows.Count).End(xlUp).Row
    ' 修正范围写法：L2到Llast_row
    Set rng = active_sheet.Range("L2:L" & last_row)
    
    packages = Array("big box", "small box")
    
    For Each cel In rng
        cell_value = Trim(cel.Value) ' 去除首尾空格，避免空格导致的匹配失败
        ' 用Match函数判断值是否在数组中，IsError表示未找到匹配
        If IsError(Application.Match(cell_value, packages, 0)) Then
            ' 可选：排除空值，如果不需要可以删除这个判断
            If cell_value <> "" Then
                cel.Interior.Color = vbRed
            End If
        End If
    Next cel
End Sub

关键优化点 #

• 使用Application.Match替代Filter，更直接判断值是否在数组中，逻辑更清晰。
• 添加Trim(cell_value)处理单元格中的首尾空格，避免因空格导致的匹配失败（比如单元格值是" big box "会被误判）。
• 可选添加空值判断，避免空单元格被标记为红色。
• 所有变量均显式声明，添加Option Explicit避免隐式声明的错误。

内容的提问来源于stack exchange，提问作者Paulina