Consulting

Results 1 to 3 of 3

Thread: Compare Multiple Columns between two workbooks and update the values in the second wo

  1. #1

    Compare Multiple Columns between two workbooks and update the values in the second wo

    I'm trying to compare multiple columns in two sheets for differences, using Column "B" as the reference unique values in both sheets.
    Comapre columns from I:P and update the values in the corresponding columns in the second sheet.

    I have found the codes for the same but that is only comparing and updating single column instead of multiple columns.
    Any help would be appreciated.
    Please help.

  2. #2
    If it works on a single column then it would also work for multi column you only need to expand the column range.

  3. #3
    This is code I'm currently using to compare and update a single column.

    Sub ValuesCheck1Column()
    Dim lookUpSheet As Worksheet, updateSheet As Worksheet
    'Transfering data to the Master Sheet
    'Master Sheet
    Set updateSheet = Workbooks("Debt Aging Report - Master Sheet-Working.xlsm").Sheets("Sheet5")
    'Latest Sheet
    Set lookUpSheet = Workbooks("Latest Report.xlsx").Sheets("Sheet1")
    'To check the values are correct on Master Sheet
    Dim LastRevRow As Long, LastAlphaRow As Long, i As Long, j As Long
    Dim RangeToUpdate As Range, SourceRng As Range, RevRngToColour As Range, AlphaRngToColour As Range
    LastRevRow = updateSheet.Cells(Rows.Count, "B").End(xlUp).Row
    LastAlphaRow = lookUpSheet.Cells(Rows.Count, "B").End(xlUp).Row
    Set RangeToUpdate = updateSheet.Range("M2:M" & LastRevRow)
    Set SourceRng = lookUpSheet.Range("P7:P" & LastAlphaRow)
    RevPO = updateSheet.Range("H2:H" & LastRevRow).Value
    AlphaPO = lookUpSheet.Range("N7:N" & LastAlphaRow).Value
    RevAD = RangeToUpdate.Value
    AlphaAD = SourceRng.Value
    For i = 1 To UBound(RevPO)
      For j = 1 To UBound(AlphaPO)
        If RevPO(i, 1) = AlphaPO(j, 1) And RevAD(i, 1) <> AlphaAD(j, 1) Then
          RevAD(i, 1) = AlphaAD(j, 1)
          If RevRngToColour Is Nothing Then Set RevRngToColour = RangeToUpdate.Cells(i) Else Set RevRngToColour = Union(RevRngToColour, RangeToUpdate.Cells(i))
        End If
      Next j
    Next i
    If Not RevRngToColour Is Nothing Then
      RangeToUpdate.Value = RevAD
      RevRngToColour.Font.Color = rgbRed
    End If
    End Sub
    The red highlighed - currently update column M in first file by comparing column P in the second file.
    I would like to update I-P columns from from file by comparing I-P from the second file.

    Any help would be appreciated.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •