Reconciliation Macro: Discrepancy Highlighting & Status Reporting

The Goal: From Raw Data to a Clear Report

Imagine you are reconciling your internal transaction records against a statement from your broker. The goal isn't just to know that there are differences, but to see precisely what they are, where they are, and what their status is.

To achieve this, we'll focus on two key enhancements: applying formatting to highlight issues and building a macro that generates a detailed output like the one shown above.

Part 1: Highlighting Discrepancies

One of the most direct ways to flag an issue is to change a cell's color. VBA gives you full control over cell formatting, allowing you to highlight discrepancies as your macro finds them. A fundamental function for this is StrComp, which compares two strings.

The macro in the article is great for comparing two lists that are already aligned side-by-side. However, in our reconciliation scenario, a missing item in one list means the rows won't align. Therefore, we need to integrate this highlighting technique into the more robust Scripting.Dictionary logic from our previous lesson.

You can do this by adding a single line to the macro we built. Inside the If Not dict.Exists... block, where you identify a discrepancy, you can add code to color the cell you are currently checking.

' (Inside the loop checking List A against the dictionary of List B)
For Each cell In listA
    If Not dict.Exists(cell.Value) Then
        ' This is a discrepancy. We already know how to write it to an output column.
        ' NOW, we also highlight the original cell.
        cell.Interior.Color = vbYellow ' vbYellow is a built-in constant for the color yellow.
        
        ' ... rest of the code to output the value
    End If
Next cell

By adding this one line, your macro now provides an immediate visual cue right in the source data, making it much easier to spot problems at a glance.

Part 2: Generating a Detailed Status Report

Highlighting is useful, but a formal reconciliation requires a separate, clear report. Your goal is to create a macro that doesn't just list the mismatched keys, but copies the entire row of data for each discrepancy and labels it with a clear status.

The key challenge is moving from finding a value to fetching its entire row, and then copying that data to a new location.

While the Stack Overflow example uses Range.Find, we can adapt its data-copying logic to our superior Scripting.Dictionary method. When our dictionary lookup identifies a discrepancy, we know the cell object we are testing. From that object, we can get its entire row (cell.EntireRow) and copy it to our output sheet.

Part 3: Building the Enhanced Reconciliation Macro

Now, let's combine these concepts—highlighting, full-row copying, and status labeling—into a single, powerful macro. The following code builds upon the two-way dictionary reconciliation from our last lesson.

It will:

1. Compare "MyTable" (in column A) and "BrokerTable" (in column B).
2. Highlight discrepancies in the original lists.
3. Create a consolidated report on a new sheet named "ReconciliationOutput".
4. Copy the full row of data for each discrepancy.
5. Add a "Status" column explaining the issue.

Sub EnhancedReconciliation()
    ' Purpose: Performs a two-way reconciliation, highlights discrepancies,
    '          and generates a consolidated output report with full details.

    Dim dict As Object
    Dim ws As Worksheet
    Dim outputSheet As Worksheet
    Dim myTable As Range, brokerTable As Range
    Dim cell As Range
    Dim lastRowA As Long, lastRowB As Long
    Dim outputRow As Long

    ' --- SETUP ---
    Set ws = ThisWorkbook.Sheets("Data") ' Assumes data is on a sheet named "Data"
    Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare ' Case-insensitive comparison

    ' Create or clear the output sheet
    On Error Resume Next ' Ignore error if the sheet doesn't exist
    Application.DisplayAlerts = False
    ThisWorkbook.Sheets("ReconciliationOutput").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
    
    Set outputSheet = ThisWorkbook.Sheets.Add(After:=ws)
    outputSheet.Name = "ReconciliationOutput"
    
    ' Set up headers for the output report
    ws.Range("A1:E1").Copy outputSheet.Range("A1") ' Assumes headers in A1:E1
    outputSheet.Range("F1").Value = "Status"
    outputRow = 2 ' Start outputting data in row 2

    ' Dynamically find the last row for each list
    lastRowA = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    lastRowB = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row ' Assuming broker data starts in C
    
    ' Define the ranges for the two lists (using just the key column for comparison)
    Set myTable = ws.Range("A2:A" & lastRowA)
    Set brokerTable = ws.Range("C2:C" & lastRowB) ' Assuming keys are in C
    
    ' Clear any previous highlighting
    ws.Range("A2:E" & lastRowA).Interior.Color = xlNone
    ws.Range("C2:G" & lastRowB).Interior.Color = xlNone ' Assuming data is in C:G

    ' --- PART 1: Find items in MyTable that are NOT in BrokerTable ---
    
    ' Load all broker IDs into the dictionary
    For Each cell In brokerTable
        If Not dict.Exists(cell.Value) And cell.Value <> "" Then
            dict.Add cell.Value, cell.Row
        End If
    Next cell
    
    ' Loop through MyTable and check against the dictionary
    For Each cell In myTable
        If Not dict.Exists(cell.Value) Then
            ' Discrepancy found!
            ' 1. Highlight the original row
            ws.Range("A" & cell.Row & ":E" & cell.Row).Interior.Color = vbYellow
            
            ' 2. Copy the full row to the output sheet
            ws.Range("A" & cell.Row & ":E" & cell.Row).Copy outputSheet.Range("A" & outputRow)
            
            ' 3. Add the status
            outputSheet.Range("F" & outputRow).Value = "Missing in Broker's Table"
            
            outputRow = outputRow + 1
        End If
    Next cell
    
    ' --- PART 2: Find items in BrokerTable that are NOT in MyTable ---
    
    ' Clear and re-populate the dictionary with MyTable IDs
    dict.RemoveAll
    For Each cell In myTable
        If Not dict.Exists(cell.Value) And cell.Value <> "" Then
            dict.Add cell.Value, cell.Row
        End If
    Next cell
    
    ' Loop through BrokerTable and check against the dictionary
    For Each cell In brokerTable
        If Not dict.Exists(cell.Value) Then
            ' Discrepancy found!
            ' 1. Highlight the original row
            ws.Range("C" & cell.Row & ":G" & cell.Row).Interior.Color = vbCyan ' Use a different color
            
            ' 2. Copy the full row to the output sheet
            ' Note: Mapping columns from broker table (C:G) to output table (A:E)
            outputSheet.Range("A" & outputRow).Value = ws.Cells(cell.Row, "C").Value ' Broker's ID
            outputSheet.Range("B" & outputRow).Value = ws.Cells(cell.Row, "D").Value ' Stock
            outputSheet.Range("C" & outputRow).Value = ws.Cells(cell.Row, "E").Value ' Qty
            outputSheet.Range("D" & outputRow).Value = ws.Cells(cell.Row, "F").Value ' Price
            outputSheet.Range("E" & outputRow).Value = ws.Cells(cell.Row, "G").Value ' Date
            
            ' 3. Add the status
            outputSheet.Range("F" & outputRow).Value = "Missing in My Table"
            
            outputRow = outputRow + 1
        End If
    Next cell
    
    ' --- FINALIZATION ---
    outputSheet.Columns("A:F").AutoFit
    Set dict = Nothing
    ws.Activate
    MsgBox "Reconciliation complete. See 'ReconciliationOutput' sheet for details."

End Sub

This macro is a significant step forward. It automates the entire process from comparison to reporting. Notice how it handles creating a new sheet, copying headers, copying full rows of data, and adding the crucial status label. This is a template you can adapt for countless reconciliation tasks in your future accounting work.

Conclusion

In this lesson, you have elevated your reconciliation macro from a simple comparison tool to a complete automated reporting solution. You have learned how to make the output of your VBA procedures clear, professional, and actionable, a skill that is paramount in any accounting or finance role.

Key Takeaways:

• You can use cell.Interior.Color to programmatically highlight data, providing immediate visual feedback on discrepancies.
• The StrComp function offers a precise way to compare text values within your VBA code.
• A comprehensive status report involves copying the entire row of a discrepant transaction, not just its key. This is done by referencing the row of the cell being checked (cell.Row) and using it to define a range to copy.
• By adding a "Status" column to your output, you provide essential context, explaining why an item has been flagged (e.g., "Missing in List A").
• Combining the efficient Scripting.Dictionary lookup with these formatting and data copying techniques results in a robust, professional-grade automation tool.

You have now built a powerful data processing engine. In our next module, we will leverage these skills to tackle an even bigger task: the complete automation of financial statements. We will begin by writing a VBA macro to clear a report sheet and generate a complete Income Statement directly from a list of raw transactions.