Excel VBA循环高亮差异单元格报错，求正确语法修正

Hey there, let's sort out that syntax error in your highlighting logic! The issue comes down to a key detail: when you assign a multi-cell range to a Variant variable (like varSheetB), it gets converted into a 2D array—not a Range object. That means you can't use .Range() on it directly, which is why your varSheetB.Range.(iRow & iCol) line is throwing an error.

Here are two solid solutions to fix this: #

Solution 1: Directly Target the Worksheet Cell (Simple & Intuitive)

Instead of trying to use the array to access the cell, directly reference the "Discrepancy Compare" worksheet's cells using your loop indices. Since your range starts at row 12, the array's iRow = 1 maps to worksheet row 12, so we need to adjust the row index accordingly:

Option Explicit
Sub Compare()
    Dim varSheetA As Variant
    Dim varSheetB As Variant
    Dim strRangeToCheck As String
    Dim iRow As Long
    Dim iCol As Long
    
    strRangeToCheck = "A12:G150"
    Debug.Print Now
    
    varSheetA = Worksheets("Main").Range(strRangeToCheck)
    varSheetB = Worksheets("Discrepancy Compare").Range(strRangeToCheck)
    Debug.Print Now
    
    For iRow = LBound(varSheetA, 1) To UBound(varSheetA, 1)
        For iCol = LBound(varSheetA, 2) To UBound(varSheetA, 2)
            If varSheetA(iRow, iCol) <> varSheetB(iRow, iCol) Then
                ' Highlight the corresponding cell in "Discrepancy Compare"
                Worksheets("Discrepancy Compare").Cells(iRow + 11, iCol).Interior.ColorIndex = 36
                ' +11 because array row 1 = worksheet row 12 (12-1=11)
            End If
        Next iCol
    Next iRow
End Sub

Alternatively, you can use the original range to get the correct cell without calculating the row offset:

Worksheets("Discrepancy Compare").Range(strRangeToCheck).Cells(iRow, iCol).Interior.ColorIndex = 36

Solution 2: Batch Highlighting (More Efficient for Large Datasets)

If you're working with a big range, repeatedly modifying cells one-by-one can slow down your code. Instead, collect all differing cells into a single Range object first, then apply the highlight in one go:

Option Explicit
Sub Compare()
    Dim varSheetA As Variant
    Dim varSheetB As Variant
    Dim strRangeToCheck As String
    Dim iRow As Long
    Dim iCol As Long
    Dim highlightRange As Range
    
    strRangeToCheck = "A12:G150"
    Debug.Print Now
    
    varSheetA = Worksheets("Main").Range(strRangeToCheck)
    varSheetB = Worksheets("Discrepancy Compare").Range(strRangeToCheck)
    Debug.Print Now
    
    Set highlightRange = Nothing ' Initialize the range variable
    
    For iRow = LBound(varSheetA, 1) To UBound(varSheetA, 1)
        For iCol = LBound(varSheetA, 2) To UBound(varSheetA, 2)
            If varSheetA(iRow, iCol) <> varSheetB(iRow, iCol) Then
                Dim currentCell As Range
                Set currentCell = Worksheets("Discrepancy Compare").Range(strRangeToCheck).Cells(iRow, iCol)
                
                ' Add the cell to our highlight range
                If highlightRange Is Nothing Then
                    Set highlightRange = currentCell
                Else
                    Set highlightRange = Union(highlightRange, currentCell)
                End If
            End If
        Next iCol
    Next iRow
    
    ' Apply the highlight to all collected cells at once
    If Not highlightRange Is Nothing Then
        highlightRange.Interior.ColorIndex = 36
    End If
End Sub

Why Your Original Code Failed #

When you run varSheetB = Worksheets("Discrepancy Compare").Range(strRangeToCheck), VBA converts the range into a 2D array where:

• varSheetB(iRow, iCol) gives the value of the cell at row iRow (relative to the start of the range) and column iCol
• The array has no connection to the original Range object, so calling .Range() on it is invalid

Either of the solutions above will get your highlighting working smoothly!

内容的提问来源于stack exchange，提问作者Jay.Kel