regex - 如何在excel VBA中使用正则表达式突出显示温度字符串

Question

当单元格的内容发生变化时，一些字符串会突出显示。
这部分我已经开始工作了。
现在我添加了一个正则表达式来突出显示包含温度的字符串(即 13° 或 10°-25°)但是这部分不起作用(没有错误，但只是没有输出)

这就是我到目前为止所拥有的。
除了这部分(blue2Items = "(\d{1,2}°-\d{1,2}°|\d{1,2}°)")之外，这一切都有效

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("J10:J80")) Is Nothing Then
        Dim objRegex As Object
        Dim RegMC As Object
        Dim RegM As Object
        '-----------------------------------------------------
        Target.Font.ColorIndex = 1
        redItems = "(RXB|RXG|RGX|RXC|RCX|RXD|RXE|RXS|RFG|RNG|RCL|RPG|RFL|RFS|RSC|RFW|ROX|ROP|RPB|RIS|RDS|RRW|RRY|RCM|ICE|MAG|RMD|RLI|RLM|RSB|RBI|RBM|ELI|ELM|CAO)"
        blueItems = "(COL|CRT)"
        greenItems = "(AVI|HEG)"
        blue2Items = "(\d{1,2}°-\d{1,2}°|\d{1,2}°)"
        '-----------------------------------------------------
        allItems = redItems & "|" & blueItems & "|" & blue2Items & "|" & greenItems
        Set objRegex = CreateObject("vbscript.regexp")
        With objRegex
            .Global = True
            .Pattern = allItems
            '-----------------------------------------------------
                'On Error Resume Next
                If .test(Range(Target.Address).Value) Then
                    Set RegMC = .Execute(Range(Target.Address).Value)
                    For Each RegM In RegMC
                        If InStr(redItems, RegM) Then
                            Range(Target.Address).Characters(RegM.FirstIndex + 1, RegM.Length).Font.Color = RGB(255, 0, 0)
                        ElseIf InStr(blueItems, RegM) Then
                            Range(Target.Address).Characters(RegM.FirstIndex + 1, RegM.Length).Font.Color = RGB(0, 59, 255)
                        ElseIf InStr(blue2Items, RegM) Then
                            Range(Target.Address).Characters(RegM.FirstIndex + 1, RegM.Length).Font.Color = RGB(0, 59, 255)
                        ElseIf InStr(greenItems, RegM) Then
                            Range(Target.Address).Characters(RegM.FirstIndex + 1, RegM.Length).Font.Color = RGB(0, 176, 80)
                        End If
                    Next
                End If
        End With
    End If


I tried the regex with a simple sub and it worked, but I cant get it to work in the above code

    Sub RegExpTemps()

    Dim objRegex As Object
    Dim RegMC As Object
    Dim RegM As Object

    Set objRegex = CreateObject("vbscript.regexp")
    With objRegex
        .Global = True
        .Pattern = "(\d{1,2}°-\d{1,2}°|\d{1,2}°)"
        For row = 10 To 80
            If .test(Cells(row, 10).Value) Then
                Set RegMC = .Execute(Cells(row, 10).Value)
                For Each RegM In RegMC
                    Cells(row, 10).Characters(RegM.FirstIndex + 1, RegM.Length).Font.Color = RGB(0, 0, 255) 'vbGreen
                Next
            End If
        Next row
    End With
End Sub

预期结果是当单元格内容更改时，包含温度(具有度数字符)的字符串会突出显示

Answer 1

定义 blue2Items作为

blue2Items = "(\d{1,2}°(?:-\d{1,2}°)?)"

它更简洁，意味着将 1 或 2 位数字与 ° 匹配在它们之后签名，然后是 - 的可选序列再次使用 ° 的 1 或 2 位数字符号。

然后，您需要根据匹配的捕获组更改字体颜色。 match.Submatches(x)让您访问这些值，如果您检查它们的长度，您将知道哪个匹配。

利用

If .test(Range(Target.Address).Value) Then
    Set RegMC = .Execute(Range(Target.Address).Value)
    For Each RegM In RegMC
        If Len(RegM.Submatches(0)) > 0 Then
            Range(Target.Address).Characters(RegM.FirstIndex + 1, RegM.Length).Font.Color = RGB(255, 0, 0)
        ElseIf Len(RegM.Submatches(1)) > 0 Then
            Range(Target.Address).Characters(RegM.FirstIndex + 1, RegM.Length).Font.Color = RGB(0, 59, 255)
        ElseIf Len(RegM.Submatches(2)) > 0 Then
            Range(Target.Address).Characters(RegM.FirstIndex + 1, RegM.Length).Font.Color = RGB(0, 59, 255)
        ElseIf Len(RegM.Submatches(3)) > 0 Then
            Range(Target.Address).Characters(RegM.FirstIndex + 1, RegM.Length).Font.Color = RGB(0, 176, 80)
        End If
    Next
End If

测试: