如何在VBA中使用xlExpression条件格式标记不符合VAT格式的单元格为红色

核心调整说明 #

• 你已有的公式是判断符合VAT规则返回TRUE，要标记不符合的内容，只要在公式外层套NOT()即可
• 已先修正原公式中的语法错误：原公式存在多处RIGHT(3)、RIGHT(11)、RIGHT(12)遗漏单元格引用的问题，已统一补全对应引用
• 条件格式中使用相对引用（不加$符号），即可自动适配应用范围内的所有单元格

完整VBA代码 #

Sub 设置VAT格式不合规标红()
    Dim rng As Range
    Dim condition1 As FormatCondition
    
    ' 此处修改为你要应用规则的单元格范围，示例为B列第2行到第1000行
    Set rng = ThisWorkbook.Sheets("替换为你的实际工作表名").Range("B2:B1000")
    
    ' 先清除该范围已有的条件格式，避免规则冲突
    rng.FormatConditions.Delete
    
    ' 整合修正后的VAT校验规则，不符合规则时触发格式
    Set condition1 = rng.FormatConditions.Add(Type:=xlExpression, Formula1:= _
        "=NOT(OR(AND(LEFT(B2,2)=""CZ"",OR(ISNUMBER(VALUE(RIGHT(B2,8))),ISNUMBER(VALUE(RIGHT(B2,10))),ISNUMBER(VALUE(RIGHT(B2,9)))),OR(LEN(B2)=10,LEN(B2)=11,LEN(B2)=12)),AND(ISNUMBER(VALUE(RIGHT(B2,3))),MID(B2,12,1)=""."",ISNUMBER(VALUE(MID(B2,9,3))),MID(B2,8,1)=""."",ISNUMBER(VALUE(MID(B2,5,3))),MID(B2,4,1)=""-"",LEFT(B2,3)=""CHE"",LEN(B2)=15),AND(LEFT(B2,2)=""BG"",OR(ISNUMBER(VALUE(RIGHT(B2,9))),ISNUMBER(VALUE(RIGHT(B2,10)))),OR(LEN(B2)=11,LEN(B2)=12)),AND(LEFT(B2,2)=""BE"",ISNUMBER(VALUE(RIGHT(B2,10))),LEN(B2)=12),AND(LEFT(B2,3)=""ATU"",ISNUMBER(VALUE(RIGHT(B2,8))),LEN(B2)=11),AND(LEFT(B2,2)=""DE"",ISNUMBER(VALUE(RIGHT(B2,9))),LEN(B2)=11),OR(AND(LEN(B2)=9,ISNUMBER(VALUE(RIGHT(B2,8))),LEFT(B2,1)=""X""),AND(LEN(B2)=9,ISNUMBER(VALUE(LEFT(B2,8))),RIGHT(B2,1)=""X""),AND(LEN(B2)=9,LEFT(B2,1)=""X"",RIGHT(B2,1)=""X"",ISNUMBER(VALUE(MID(B2,2,7))))),AND(LEFT(B2,2)=""EL"",ISNUMBER(VALUE(RIGHT(B2,9))),LEN(B2)=11),AND(LEFT(B2,2)=""EE"",ISNUMBER(VALUE(RIGHT(B2,9))),LEN(B2)=11),AND(LEFT(B2,2)=""DK"",ISNUMBER(VALUE(RIGHT(B2,8))),LEN(B2)=10),AND(LEFT(B2,2)=""HR"",ISNUMBER(VALUE(RIGHT(B2,11))),LEN(B2)=13),AND(LEFT(B2,2)=""GB"",ISNUMBER(VALUE(RIGHT(B2,9))),LEN(B2)=11),OR(AND(ISNUMBER(VALUE(RIGHT(B2,9))),NOT(ISNUMBER(VALUE(MID(B2,3,1)))),LEFT(B2,2)=""FR"",LEN(B2)=13),AND(ISNUMBER(VALUE(RIGHT(B2,9))),NOT(ISNUMBER(VALUE(LEFT(B2,4)))),LEFT(B2,2)=""FR"",LEN(B2)=13),AND(ISNUMBER(VALUE(RIGHT(B2,10))),NOT(ISNUMBER(VALUE(LEFT(B2,3)))),LEFT(B2,2)=""FR"",LEN(B2)=13),AND(ISNUMBER(VALUE(RIGHT(B2,11))),LEFT(B2,2)=""FR"",LEN(B2)=13)),AND(LEFT(B2,2)=""FI"",ISNUMBER(VALUE(RIGHT(B2,8))),LEN(B2)=10),AND(LEN(B2)=14,LEFT(B2,2)=""NO"",ISNUMBER(VALUE(MID(B2,3,9))),RIGHT(B2,3)=""MVA""),AND(LEN(B2)=14,LEFT(B2,2)=""NL"",ISNUMBER(VALUE(MID(B2,3,9))),MID(B2,12,1)=""B"",ISNUMBER(VALUE(RIGHT(B2,2)))),AND(LEFT(B2,2)=""LU"",ISNUMBER(VALUE(RIGHT(B2,8))),LEN(B2)=10),AND(LEFT(B2,2)=""IT"",ISNUMBER(VALUE(RIGHT(B2,11))),LEN(B2)=13),AND(LEFT(B2,2)=""IL"",ISNUMBER(VALUE(RIGHT(B2,8))),ISNUMBER(VALUE(RIGHT(B2,8)))),OR(AND(NOT(ISNUMBER(VALUE(RIGHT(B2,2)))),ISNUMBER(VALUE(LEFT(B2,7))),LEN(B2)=9),AND(ISNUMBER(VALUE(MID(B2,3,4))),ISNUMBER(VALUE(LEFT(B2,1))),NOT(ISNUMBER(RIGHT(B2,1))),NOT(ISNUMBER(VALUE(MID(B2,2,0)))),LEN(B2)=8),AND(NOT(ISNUMBER(VALUE(RIGHT(B2,1)))),ISNUMBER(VALUE(LEFT(B2,7))),LEN(B2)=8)),AND(LEFT(B2,2)=""HU"",ISNUMBER(VALUE(RIGHT(B2,8))),LEN(B2)=10),AND(ISNUMBER(VALUE(RIGHT(B2,8))),LEFT(B2,2)=""MT"",LEN(B2)=10),OR(AND(ISNUMBER(VALUE(RIGHT(B2,12))),LEFT(B2,2)=""LT"",LEN(B2)=14),AND(ISNUMBER(VALUE(RIGHT(B2,9))),LEFT(B2,2)=""LT"",LEN(B2)=11)),AND(ISNUMBER(VALUE(RIGHT(B2,11))),LEFT(B2,2)=""LV"",LEN(B2)=13),AND(NOT(ISNUMBER(RIGHT(B2,1))),ISNUMBER(VALUE(MID(B2,3,8))),LEFT(B2,2)=""CY"",LEN(B2)=11),AND(ISNUMBER(VALUE(RIGHT(B2,10))),LEFT(B2,2)=""SK"",LEN(B2)=12),AND(ISNUMBER(VALUE(RIGHT(B2,7))),LEFT(B2,2)=""SI"",LEN(B2)=10),AND(ISNUMBER(VALUE(RIGHT(B2,10))),LEFT(B2,2)=""SE"",LEN(B2)=14),OR(AND(ISNUMBER(VALUE(RIGHT(B2,2))),LEFT(B2,2)=""RO"",LEN(B2)=4),AND(ISNUMBER(VALUE(RIGHT(B2,3))),LEFT(B2,2)=""RO"",LEN(B2)=5),AND(ISNUMBER(VALUE(RIGHT(B2,4))),LEFT(B2,2)=""RO"",LEN(B2)=6),AND(ISNUMBER(VALUE(RIGHT(B2,5))),LEFT(B2,2)=""RO"",LEN(B2)=7),AND(ISNUMBER(VALUE(RIGHT(B2,6))),LEFT(B2,2)=""RO"",LEN(B2)=8),AND(ISNUMBER(VALUE(RIGHT(B2,7))),LEFT(B2,2)=""RO"",LEN(B2)=9),AND(LEN(B2)=10,LEFT(B2,2)=""RO"",ISNUMBER(VALUE(RIGHT(B2,8)))),AND(ISNUMBER(VALUE(RIGHT(B2,9))),LEFT(B2,2)=""RO"",LEN(B2)=11),AND(ISNUMBER(VALUE(RIGHT(B2,10))),LEFT(B2,2)=""RO"",LEN(B2)=12)),AND(ISNUMBER(VALUE(MID(B2,3,9))),LEFT(B2,2)=""PT"",LEN(B2)=11),AND(ISNUMBER(VALUE(MID(B2,3,10))),LEFT(B2,2)=""PL"",LEN(B2)=12)))"
    
    ' 设置触发条件时的格式：字体标红
    With condition1
        .Font.Color = vbRed
        .Font.Bold = False ' 需要加粗可改为True
    End With
End Sub

使用说明 #

• 把代码里的替换为你的实际工作表名改成你要操作的工作表名称，比如Sheet1
• 把Range("B2:B1000")改成你需要应用校验规则的实际单元格范围
• 如果你的规则应用范围的第一个单元格不是B2，把公式里所有的B2替换成对应范围的第一个单元格地址即可

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