VBA中Or语句报错求助：条件格式红/橙单元格阻止保存功能异常

Hey there! Let's get that save-blocking logic working properly—you're close, just a couple of key issues to fix:

1. Incorrect Color Value Syntax #

VBA doesn't recognize the #FF9900 hex format you used for orange. Instead, use one of these reliable, VBA-friendly methods:

• Most readable option: Use the RGB() function: RGB(255, 153, 0) (this matches the #FF9900 orange you need)
• Hex alternative: Use VBA's BGR-order hex notation: &H0099FF (note the reversed color order compared to web hex codes)

2. Inefficient Full Range Loop (Bonus Fix) #

Looping through every cell in UsedRange can slow down your code, especially with large datasets. We can optimize this by targeting only cells that have conditional formatting applied.

3. Incomplete Error Message #

Your message only mentions red cells, but you're checking for orange too—update it to inform users about both highlighted colors.

Corrected Code #

Here's the revised version that fixes all these issues:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim rng As Range
    Dim cell As Range
    
    ' Speed up execution by turning off screen updates
    Application.ScreenUpdating = False
    
    ' Target only cells with conditional formatting (avoids looping empty cells)
    On Error Resume Next ' Handle case where no cells have conditional formatting
    Set rng = Worksheets(1).UsedRange.SpecialCells(xlCellTypeAllFormatConditions)
    On Error GoTo 0
    
    If Not rng Is Nothing Then
        For Each cell In rng
            ' Check for valid red or orange color values
            If cell.DisplayFormat.Interior.Color = vbRed _
               Or cell.DisplayFormat.Interior.Color = RGB(255, 153, 0) Then
                MsgBox "Please correct any fields highlighted in red or orange before saving.", vbExclamation
                Cancel = True
                Application.ScreenUpdating = True
                Exit Sub
            End If
        Next cell
    End If
    
    ' Re-enable screen updates and allow save if no issues found
    Application.ScreenUpdating = True
End Sub

Quick Additional Notes: #

• DisplayFormat works great for conditional formatting colors, but it will throw errors if the worksheet is protected. If your sheet is protected, add a line to temporarily unprotect it (and re-protect afterward) in the code.
• The On Error Resume Next line prevents crashes when there are no cells with conditional formatting applied.

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