How to loop through columns in two sheets and compare in VBA-CodePudding

As title says, my latest attempt will compare a column to the other column and iterate through each row. However, I am stuck on how to go to the next columns to iterate through.

    Dim i As Long
    Dim s1, s2 As Worksheet
    
    Set s1 = Worksheets("sheet1")
    Set s2= Worksheets("sheet2")
   
    For i = 2 To Rows.Count
        If IsEmpty(s1.Range("B" & i)) Then
            Exit For
        End If
        If s1.Range("B" & i).Value <> s2.Range("B" & i).Value Then
                s1.Range("B" & i).Interior.Color = vbYellow
                
        End If
    Next i

CodePudding user response：

Just another loop before, but you'll need to change your syntax away from RangeB and use cell which is numeric for columns. Assuming you're starting with B, and to all columns in sheet... something like this:

For C = 2 to sh1.usedrange.columns.count
      For i = 2 To sh1.usedrange.rows.count 
        If IsEmpty(s1.cells(i,c)) Then
            Exit For
        End If
        If s1.cells(i,c).Value <> s2.cells(i,c).Value Then
                s1.cells(i,c).Interior.Color = vbYellow
                
        End If
    Next i
next C

I didn't test, but the concept seems like what you're looking for?

CodePudding user response：

Highlight Differences

A Quick Fix (For...Next)

Sub HighlightDifferences()

    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    ' Target
    
    ' This is the target worksheet, the worksheet to be highlighted.
    Dim tws As Worksheet: Set tws = wb.Worksheets("Sheet1")
    ' If you have a nice table (one row of headers, no empty rows or columns),
    ' and it starts with cell 'A1', you can reference the whole range with:
    Dim trg As Range: Set trg = tws.Range("A1").CurrentRegion
    Dim rCount As Long: rCount = trg.Rows.Count
    Dim cCount As Long: cCount = trg.Columns.Count
    ' Clear the previous formats.
    trg.Resize(rCount - 1, cCount - 1).Offset(1, 1).ClearFormats
    
    ' Source
    
    ' This is the source worksheet, the worksheet to be compared to.
    Dim sws As Worksheet: Set sws = wb.Worksheets("Sheet2")
    ' Reference the same range as the destination range:
    Dim srg As Range: Set srg = sws.Range(trg.Address)
    
    ' Highlight.
    
    Dim tCell As Range, sCell As Range
    Dim r As Long, c As Long
    
    ' By Columns (one column at a time)
    For c = 2 To cCount
        For r = 2 To rCount
            Set tCell = trg.Cells(r, c)
            Set sCell = srg.Cells(r, c)
            If tCell.Value <> sCell.Value Then
                tCell.Interior.Color = vbYellow
            End If
        Next r
    Next c

'    ' By Rows (one row at a time)
'    For r = 2 To rCount ' ***
'        For c = 2 To cCount ' ***
'            Set tCell = trg.Cells(r, c)
'            Set sCell = srg.Cells(r, c)
'            If tCell.Value = sCell.Value Then
'                tCell.Interior.Color = vbYellow
'            End If
'        Next c ' ***
'    Next r ' ***

End Sub

A Quick Fix (For Each...Next)

Sub HighlightDifferencesForEach()

    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    ' Target
    
    ' This is the target worksheet, the worksheet to be highlighted.
    Dim tws As Worksheet: Set tws = wb.Worksheets("Sheet1")
    ' If you have a nice table (one row of headers, no empty rows or columns),
    ' and it starts with cell 'A1', you can reference the whole range with:
    Dim trg As Range: Set trg = tws.Range("A1").CurrentRegion
    ' Reference the range without the first row and the first column.
    Set trg = trg.Resize(trg.Rows.Count - 1, trg.Columns.Count - 1).Offset(1, 1)
    ' Clear the previous formats.
    trg.ClearFormats
    
    ' Source
    
    ' This is the source worksheet, the worksheet to be compared to.
    Dim sws As Worksheet: Set sws = wb.Worksheets("Sheet2")
    
    ' Highlight.
    
    Dim tCell As Range, sCell As Range
    
    ' By Rows (one row at a time)
    For Each tCell In trg.Cells
        Set sCell = sws.Range(tCell.Address)
        If tCell.Value <> sCell.Value Then
            tCell.Interior.Color = vbYellow
        End If
    Next tCell

End Sub