|
- Sub c_color()
- Dim x, k, j, i, yyy, m As Integer
- Dim rust, str_ru As Match
- Dim sr, srr, ss As String
- Dim yy As Range
- Dim reg As New RegExp
- Dim arr1
- '重置格式
- Columns("AD:AI").Font.ThemeColor = xlThemeColorLight1
- Columns("AD:AI").Font.Bold = False
- x = Range("i10000").End(xlUp).Row '数据区域最末行
- k = 0
- With reg
- .Global = True
- .Pattern = "\d{2}"
- For i = 2 To x '对数据区域每一行循环
- ReDim arr1(1 To 1)
- For Each yy In Range(Cells(i, 9), Cells(i, 28))
- k = k + 1
- ReDim Preserve arr1(1 To k)
- arr1(k) = yy.Value
- Next
- srr = VBA.Join(arr1, "-") '数据区域的每一行联合成字符串
- For j = [ad2].Column To [ai2].Column '对结果区域循环
- sr = Cells(i, j).Value
- If .Test(sr) Then ' 若匹配成功,执行
- Set rust = .Execute(sr)
- For Each str_ru In rust
- ss = str_ru.Value
- yyy = str_ru.FirstIndex '每个数字的位置
- m = VBA.InStr(1, srr, ss) '查询在数据区域是否存在
- If m > 0 Then '若存在,设置字的颜色为红+加粗
- Cells(i, j).Characters(yyy + 1, 2).Font.ColorIndex = 3 'yyy下标是从0开始,所以加1
- Cells(i, j).Characters(yyy + 1, 2).Font.Bold = True
- End If
- Next
- End If
- Next
- Erase arr1 '重置数组
- k = 0 '重置计数
- Next
- End With
- End Sub
复制代码 |
|