Faster VBA code comparing cells in two worksheets & highlighting the differences separately for modified values and newly added values

  Kiến thức lập trình

I am trying to write a VBA code which compares two worksheets and highlights the changes made. The two worksheets are separate versions of same dataset being periodically updated and I need to keep a track of changes being made. The changes should be highlighted separately for modified values and newly added values (cell colour and font colour pre-defined by user). My code is working fine for a small sample of 100 rows but the original file has ~7000 rows and it takes an eternity to run the code for the whole dataset. Would really appreciate the help from the experts. Thank you!!

P.S.: I have also added a progress bar for better user interface as the code was taking sometime to run.

Sub Highlight()

    Dim DataRange As Range
    Dim SelCell As Range
    Dim CurVer As String
    Dim CompVer As String
    Dim IntColMod As Long
    Dim IntColAdd As Long
    Dim FontColMod As Long
    Dim FontColAdd As Long
    Dim ModCellCount As Long
    Dim AddCellCount As Long

    'Pre-defined highlight format
    IntColMod = Worksheets("Version recon").Range("Modified_Value_Format").Interior.Color
    FontColMod = Worksheets("Version recon").Range("Modified_Value_Format").Font.Color
    IntColAdd = Worksheets("Version recon").Range("Added_Value_Format").Interior.Color
    FontColAdd = Worksheets("Version recon").Range("Added_Value_Format").Font.Color
    ModCellCount = 0
    AddCellCount = 0
    
    CurVer = Worksheets("Version recon").Range("Current_Version").Value
    CompVer = Worksheets("Version recon").Range("Compare_Version").Value
    'Debug.Print CurVer
    'Debug.Print CompVer

    Worksheets(CurVer).Activate
    Worksheets(CurVer).Unprotect
    Set DataRange = ActiveCell.CurrentRegion
    'Debug.Print DataRange.Address
    
    Dim CellCount As Long
    CellCount = DataRange.Cells.Count
    'Debug.Print CellCount

    Code_Progress_Bar.Show
    Dim i As Long
    i = 1
     
    For Each SelCell In DataRange

    If SelCell.Text <> Worksheets(CompVer).Range(SelCell.Address).Text And IsEmpty(Worksheets(CompVer).Range(SelCell.Address).Value) Then
        Worksheets(CurVer).Range(SelCell.Address).Interior.Color = IntColAdd
        Worksheets(CurVer).Range(SelCell.Address).Font.Color = FontColAdd
        AddCellCount = AddCellCount + 1
        Debug.Print SelCell.Address

    ElseIf SelCell.Text <> Worksheets(CompVer).Range(SelCell.Address).Text Then
        Worksheets(CurVer).Range(SelCell.Address).Interior.Color = IntColMod
        Worksheets(CurVer).Range(SelCell.Address).Font.Color = FontColMod
        ModCellCount = ModCellCount + 1
        Debug.Print SelCell.Address

    End If

    DoEvents
    Code_Progress_Bar.Label2.Width = Code_Progress_Bar.Label1.Width * i / CellCount
    Code_Progress_Bar.Label3.Caption = Format(i / CellCount * 100, "#.0") & "% completed."
    i = i + 1
     
    Next SelCell

    Unload Code_Progress_Bar
    Beep

    MsgBox "All changes are highlighted in Tab: " & CurVer, , "Code run completed"
    
    Worksheets(CurVer).Protect
    ActiveWorkbook.Sheets("Version Recon").Range("ModifiedCellCount").Value = ModCellCount
    ActiveWorkbook.Sheets("Version Recon").Range("NewlyAddedCellCount").Value = AddCellCount
    'ActiveWorkbook.Sheets("Version Recon").Range("Current_Version").Value = SheetRename

End Sub

8

Option Explicit

Sub Highlight()
    
    Dim CurVer As String, CompVer As String
    Dim IntColMod As Long, IntColAdd As Long
    Dim FontColMod As Long, FontColAdd As Long
    Dim ModCellCount As Long, AddCellCount As Long
    Dim t0 As Single: t0 = Timer
    
    ModCellCount = 0
    AddCellCount = 0
    
    'Pre-defined highlight format
    With Worksheets("Version recon")
        IntColMod = .Range("Modified_Value_Format").Interior.Color
        FontColMod = .Range("Modified_Value_Format").Font.Color
        IntColAdd = .Range("Added_Value_Format").Interior.Color
        FontColAdd = .Range("Added_Value_Format").Font.Color
        
        CurVer = .Range("Current_Version").Value
        CompVer = .Range("Compare_Version").Value
    End With
   
    ' arrays to compare values
    Dim arCur, arComp, sAddr As String, i As Long, j As Long
    Dim cel As Range, rng0 As Range, rngAdd As Range, rngMod As Range
    With Worksheets(CurVer)
        .Activate
        .Unprotect
        sAddr = .UsedRange.Address
        Set rng0 = .UsedRange.Cells(1, 1) ' top left
        arCur = .Range(sAddr).Value
        'Debug.Print sAddr
    End With
    arComp = Worksheets(CompVer).Range(sAddr).Value
    
    For i = 1 To UBound(arCur)
        For j = 1 To UBound(arCur, 2)
           ' compare
           If IsError(arComp(i, j)) Then arComp(i, j) = "#N/A"
           If IsError(arCur(i, j)) Then arCur(i, j) = "#N/A"

           If arComp(i, j) <> arCur(i, j) Then
              Set cel = rng0.Offset(i - 1, j - 1)
              ' add or mod
              If Len(arComp(i, j)) = 0 Then
                 If rngAdd Is Nothing Then
                     Set rngAdd = cel
                 Else
                     Set rngAdd = Union(rngAdd, cel)
                 End If
                 AddCellCount = AddCellCount + 1
              Else
                 If rngMod Is Nothing Then
                     Set rngMod = cel
                 Else
                     Set rngMod = Union(rngMod, cel)
                 End If
                 ModCellCount = ModCellCount + 1
              End If
           End If
        Next
    Next
    
    ' color adds
    If Not rngAdd Is Nothing Then
        rngAdd.Interior.Color = IntColAdd
        rngAdd.Font.Color = FontColAdd
        Debug.Print "Add", AddCellCount, rngAdd.Address
    End If
    
    ' color mods
    If Not rngMod Is Nothing Then
        rngMod.Interior.Color = IntColMod
        rngMod.Font.Color = FontColMod
        Debug.Print "Mod", ModCellCount, rngMod.Address
    End If
    
    MsgBox "All changes are highlighted in Tab: " & CurVer, vbInformation, _
    "Code run in " & Format(Timer - t0, "0.0 secs")
    
    Worksheets(CurVer).Protect
    With ActiveWorkbook.Sheets("Version Recon")
        .Range("ModifiedCellCount").Value = ModCellCount
        .Range("NewlyAddedCellCount").Value = AddCellCount
        '.Range("Current_Version").Value = SheetRename ??
    End With
End Sub

3

Loading data into an array and performing comparisons is a more efficient approach. You might not even need a progress bar anymore.

Note: The code is untested due to the lack of sample data.

Option Explicit

Sub Highlight()
    Dim CurVer As String
    Dim CompVer As String
    Dim IntColMod As Long
    Dim IntColAdd As Long
    Dim FontColMod As Long
    Dim FontColAdd As Long
    Dim verSht As Worksheet, currSht As Worksheet, CompSht As Worksheet
    Set verSht = Worksheets("Version recon")
    IntColMod = verSht.Range("Modified_Value_Format").Interior.Color
    FontColMod = verSht.Range("Modified_Value_Format").Font.Color
    IntColAdd = verSht.Range("Added_Value_Format").Interior.Color
    FontColAdd = verSht.Range("Added_Value_Format").Font.Color
    CurVer = verSht.Range("Current_Version").Value
    CompVer = verSht.Range("Compare_Version").Value
    Set currSht = Worksheets(CurVer)
    Set CompSht = Worksheets(CompVer)
    currSht.Unprotect
    Dim arrCurr, arrComp, ltRow As Long, ltCol As Long
    With currSht.UsedRange
        ' get row# and col# of the top-left cell (table may not start from A1)
        ltRow = .Cells(1).Row
        ltCol = .Cells(1).Column
        ' load data into array
        arrCurr = .Value
        arrComp = CompSht.Range(.Address).Value
    End With
    Dim r As Long, c As Long, addRng As Range, modRng As Range
    ' loop through cells
    For r = 1 To UBound(arrCurr)
        For c = 1 To UBound(arrCurr, 2)
            ' *** Update ***
            If CStr(arrCurr(r, c)) <> CStr(arrComp(r, c)) Then ' found different
                If Len(CStr(arrComp(r, c))) = 0 Then ' new added content
                    MergeRng addRng, currSht.Cells(r + ltRow - 1, c + lrCol - 1)
                Else ' modify
                    MergeRng modRng, currSht.Cells(r + ltRow - 1, c + lrCol - 1)
                End If
            End If
        Next
    Next
    ' apply formatting
    If Not addRng Is Nothing Then
        With addRng
            .Interior.Color = IntColAdd
            .Font.Color = FontColAdd
            verSht.Range("NewlyAddedCellCount").Value = .Cells.Count
        End With
    End If
    If Not modRng Is Nothing Then
        With modRng
            .Interior.Color = IntColMod
            .Font.Color = FontColMod
            verSht.Range("ModifiedCellCount").Value = .Cells.Count
        End With
    End If
    MsgBox "All changes are highlighted in Tab: " & CurVer, , "Code run completed"
    Worksheets(CurVer).Protect
End Sub
' helper UDF to merge two Range objects
Function MergeRng(ByRef RngAll As Range, ByRef RngSub As Range) As Range
    If RngAll Is Nothing Then
        Set RngAll = RngSub
    ElseIf Not RngSub Is Nothing Then
        Set RngAll = Application.Union(RngAll, RngSub)
    End If
    Set MergeRng = RngAll
End Function

4

Adding Conditional Formatting Rules is often faster then formatting many cells individually. But you may want to consider deleting the Conditional Formatting after your review.

Option Explicit

Function GetCurrentWorksheet() As Worksheet
    Dim Name As String
    Name = Worksheets("Version recon").Range("Current_Version").Value
    Set GetCurrentWorksheet = Worksheets(Name)
End Function

Function GetCompareWorksheet() As Worksheet
    Dim Name As String
    Name = Worksheets("Version recon").Range("Compare_Version").Value
    Set GetCompareWorksheet = Worksheets(Name)
End Function

Sub AddConditionalFormattingRules()
    Dim t As Double: t = Timer
    Dim wsCurrentWorksheet As Worksheet
    Dim wsCompareWorksheet As Worksheet
    Dim Target As Range
    Dim CurrentSheetName As String
    Dim CompareSheetName As String
    Dim StartCellAddress As String

    ' Define worksheets
    Set wsCurrentWorksheet = GetCurrentWorksheet
    Set wsCompareWorksheet = GetCompareWorksheet
    CurrentSheetName = wsCurrentWorksheet.Name
    CompareSheetName = wsCompareWorksheet.Name

    ' Define the range in the "Compare" sheet for conditional formatting
    With wsCompareWorksheet
        Set Target = Union(.UsedRange, .Range(wsCurrentWorksheet.UsedRange.Address))
    End With

    ' Get the address of the first cell in the target range
    StartCellAddress = Target.Cells(1, 1).Address(RowAbsolute:=False, ColumnAbsolute:=False)

    ' Clear existing conditional formatting
    Target.FormatConditions.Delete
    
    ' Rule 1: If the cell in the current sheet is empty and the corresponding cell in the compare sheet is not empty, apply "Neutral"
    With Target.FormatConditions.Add(Type:=xlExpression, Formula1:="=AND(ISBLANK('" & CurrentSheetName & "'!" & StartCellAddress & "),NOT(ISBLANK('" & CompareSheetName & "'!" & StartCellAddress & ")))")
        .Interior.Color = Worksheets("Version recon").Range("Added_Value_Format").Interior.Color
        .Font.Color = Worksheets("Version recon").Range("Added_Value_Format").Font.Color
    End With
    
    ' Rule 2: If the cell in the current sheet is not empty and differs from the corresponding cell in the compare sheet, apply "Bad"
    With Target.FormatConditions.Add(Type:=xlExpression, Formula1:="=AND(NOT(ISBLANK('" & CurrentSheetName & "'!" & StartCellAddress & ")),'" & CurrentSheetName & "'!" & StartCellAddress & " <> '" & CompareSheetName & "'!" & StartCellAddress & ")")
        .Interior.Color = Worksheets("Version recon").Range("Modified_Value_Format").Interior.Color
        .Font.Color = Worksheets("Version recon").Range("Modified_Value_Format").Font.Color
    End With
    
    Debug.Print Timer - t

    ' MsgBox "Conditional formatting rules applied.", vbInformation
End Sub

2

Theme wordpress giá rẻ Theme wordpress giá rẻ Thiết kế website

LEAVE A COMMENT