如何修改VBA COUNTINGCOLORS函数，实现颜色与名称匹配统计

需求说明 #

原函数用于统计指定范围内背景色与参考单元格一致的单元格数量，现需新增MYNAME参数，统计同时满足背景色匹配且单元格值包含指定人名的单元格数量（单元格值格式为「名称+数字」，如Bob 1、John 12）。

原修改代码的问题 #

• 未获取参考单元格的背景色索引，COLORCELL变量未赋值
• VBA无Contains关键字，需用InStr函数判断字符串包含关系
• MYNAME参数设为Range类型不合理，改为String类型更贴合参数用途
• 存在多余的Set CELL = MYRANGE语句，且循环变量未声明
• If语句缺少Then关键字，语法不完整

修正后的代码 #

Option Explicit

Function COUNTINGCOLORS(MYRANGE As Range, MYCOLOR As Range, MYNAME As String) As Integer
    Dim COLORCELL As Integer
    Dim CURRENTCOUNT As Integer
    Dim CELL As Range
    
    ' 获取参考单元格的背景色索引
    COLORCELL = MYCOLOR.Interior.ColorIndex
    
    ' 遍历目标区域
    For Each CELL In MYRANGE
        ' 判断：背景色匹配 且 单元格值包含指定人名
        If CELL.Interior.ColorIndex = COLORCELL And InStr(CELL.Value, MYNAME) > 0 Then
            CURRENTCOUNT = CURRENTCOUNT + 1
        End If
    Next CELL
    
    ' 返回统计结果
    COUNTINGCOLORS = CURRENTCOUNT
End Function

关键修改说明 #

• 新增Option Explicit强制变量声明，避免未声明变量的错误
• 将MYNAME参数类型改为String，直接接收人名文本，使用更便捷（若需保留Range类型，可将判断语句改为InStr(CELL.Value, MYNAME.Value) > 0）
• 使用InStr(CELL.Value, MYNAME) > 0判断单元格值是否包含指定人名，InStr函数返回匹配位置，大于0表示存在包含关系
• 补充COLORCELL的赋值语句，正确获取参考单元格的背景色索引
• 完善If语句的语法结构，添加Then关键字
• 声明循环变量CELL，符合VBA规范

使用示例 #

在Excel单元格中输入公式：
=COUNTINGCOLORS(A1:A10, C1, "Bob")
表示统计A1:A10范围内，背景色与C1一致且单元格值包含Bob的单元格数量。

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