VBA遍历工作表设置带变量的条件格式时公式报错求助

Hey there! Let's sort out that conditional formatting formula headache in your VBA code. I see the issue—you're trying to apply a rule where cells in column R are highlighted if they're more than twice the average of the entire used range in R, but you're running into problems with quotes and syntax errors.

The Root of the Problem #

The main issue is how VBA handles conditional formatting formulas:

• If you wrap the formula in extra quotes, Excel will treat it as text instead of a valid formula, which breaks the rule.
• If you skip wrapping the formula in VBA's double quotes entirely, VBA will interpret it as a VBA expression (not an Excel formula), triggering that "Expected: End of Statement" error.

Fixed VBA Code #

Here's a revised script that solves this. We'll loop through all sheets except "Base Details", find the last used row in column R, build the correct formula as a string, and apply the conditional formatting properly:

Sub ApplyConditionalFormattingToRColumn()
    Dim targetSheet As Worksheet
    Dim lastRowInR As Long
    Dim cfFormula As String
    
    ' Loop through every worksheet in the workbook
    For Each targetSheet In ThisWorkbook.Worksheets
        ' Skip the "Base Details" sheet
        If targetSheet.Name <> "Base Details" Then
            ' Find the last row with data in column R
            lastRowInR = targetSheet.Cells(targetSheet.Rows.Count, "R").End(xlUp).Row
            
            ' Skip if there's no data to format (e.g., only a header row)
            If lastRowInR < 2 Then GoTo NextSheet
            
            ' Build the Excel formula as a string
            ' Absolute references ($) ensure the average range doesn't shift per cell
            cfFormula = "=R2>2*AVERAGE($R$2:$R$" & lastRowInR & ")"
            
            ' Clear existing conditional formatting to avoid duplicate rules
            targetSheet.Range("R2:R" & lastRowInR).FormatConditions.Delete
            
            ' Apply the new conditional formatting rule
            With targetSheet.Range("R2:R" & lastRowInR).FormatConditions.Add( _
                Type:=xlExpression, Formula1:=cfFormula)
                ' Customize your format here—this example uses red fill + bold text
                .Interior.Color = RGB(255, 0, 0)
                .Font.Bold = True
            End With
        End If
NextSheet:
    Next targetSheet
End Sub

Key Details to Note #

1. Formula Construction: We concatenate lastRowInR into the formula string to dynamically set the average range. The $ signs make this range absolute, so every cell in column R uses the same average (from R2 to the last data row) instead of shifting the range as the rule applies to different cells.
2. Quote Handling: The formula is wrapped in VBA's double quotes, but there are no extra quotes inside the formula itself—this is exactly what Excel expects for a valid conditional formatting rule.
3. Cleanup: Clearing existing conditional formatting first prevents overlapping rules or unexpected behavior from old formatting.

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