不依赖区域日期格式的Excel VBA表格格式化宏兼容问题咨询

问题根因 #

• 你当前写的条件格式公式使用了本地区域设置的规则：参数分隔符用了分号;，且直接写入的英文函数名在非英语区域环境下无法被Excel识别
• 周末标灰失效本质是文本型日期无法被WEEKDAY函数正确解析，该函数本身只读取日期的存储数值，和显示格式无关，只要是标准数值型日期，不管系统显示格式是日/月/年还是其他格式，都能正常计算

适配方案 #

1. VBA设置条件格式的Formula1参数时，统一使用美式规则编写公式：函数名用英文、参数分隔符用英文逗号,，Excel会自动将公式转换为当前系统语言、区域对应的格式，无需手动适配
2. 提前校验表头行（F1及右侧的日期行）的格式，确保所有日期都是数值型日期而非文本，避免WEEKDAY解析失败
3. 原有代码里大量的Select操作可以优化，避免运行报错同时提升效率

修改后的核心代码片段（仅展示修改的条件格式部分） #

'------------------------------Set conditional format for the weekend - grey filling--------------------------------------
' 改用美式规则写公式，逗号分隔，英文函数
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=OR(WEEKDAY(F$1,2)=7,WEEKDAY(F$1,2)=6)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = -0.249946592608417
End With
Selection.FormatConditions(1).StopIfTrue = False

'------------------------------Set conditional format for Arrivals - blue filling--------------------------------------
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=AND(F2>0,$E2=""A"")"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .Color = 15773696
    .TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False

'------------------------------Set conditional format for shortage - red filling--------------------------------------
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=AND(F2<0,$E2=""I"")"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
    .Color = -16383844
    .TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .Color = 13551615
    .TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False

如果还是存在日期识别问题，可以在冻结窗格代码前增加一行代码，将表头日期强制转成数值格式：

' 强制转换F1及右侧表头为日期数值格式
Range(Range("F1"), Range("F1").End(xlToRight)).NumberFormat = "yyyy/m/d"

附加优化建议 #

原有代码中大量使用Select、Selection操作，容易因为用户选中位置不对导致宏运行出错，可以替换为直接引用工作表对象、范围对象，减少操作依赖。

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