Automate Data reconciliation processes in Excel using VBA

The goal is to compare two sets of data (e.g., bank statements and accounting entries) and highlight matches and discrepancies.

Objective:

This automation process will compare two lists of data, for example, one in column A and another in column B. The result will highlight matching values in green and non-matching values in red.

Steps:

1. Load Data:
   • Assume we have two datasets: one in column A (e.g., bank transactions) and the other in column B (e.g., accounting entries).
2. Reconciliation:
   • Compare each value in column A with the values in column B.
3. Highlight Matches and Discrepancies:
   • If a value in column A is found in column B, color it green (match found).
   • If a value in column A is not found in column B, color it red (no match).
4. Additional Options:
   • You can add functionality for handling duplicates or certain errors.

VBA Code:

Sub DataReconciliation()
    Dim ws As Worksheet
    Dim rangeA As Range, rangeB As Range
    Dim cellA As Range, cellB As Range
    Dim matchFound As Boolean
    ' Define the active sheet (in this case, "Sheet1")
    Set ws = ThisWorkbook.Sheets("Sheet1")
    ' Define the data ranges (e.g., A2:A100 and B2:B100)
    Set rangeA = ws.Range("A2:A100")
    Set rangeB = ws.Range("B2:B100")
    ' Clear any existing background color
    rangeA.Interior.ColorIndex = -4142
    ' Loop through each cell in range A
    For Each cellA In rangeA
        matchFound = False ' Initialize flag to check for a match  
        ' Compare with each cell in range B
        For Each cellB In rangeB
            If cellA.Value = cellB.Value Then
                matchFound = True
                Exit For ' Once a match is found, exit the loop
            End If
        Next cellB       
        ' If a match was found
        If matchFound Then
            cellA.Interior.Color = RGB(144, 238, 144 ' Light green for a match
        Else
            cellA.Interior.Color = RGB(255, 99, 71) ' Light red for no match
        End If
    Next cellA   
    MsgBox "Reconciliation Completed!", vbInformation
End Sub

Detailed Explanation:

1. Variable Definition:
   • ws: Represents the active worksheet where the data is located.
   • rangeA and rangeB: Define the ranges for the two sets of data to compare (columns A and B).
   • cellA and cellB: Used to loop through the cells in rangeA and rangeB.
   • matchFound: A flag used to check whether a match is found between values.
2. Defining the Worksheet and Ranges:
   • Set ws = ThisWorkbook.Sheets(« Sheet1 ») sets the worksheet where the data is stored (you can change « Sheet1 » to the name of your sheet).
   • Set rangeA = ws.Range(« A2:A100 ») defines the range of cells in column A (adjust as needed).
   • Set rangeB = ws.Range(« B2:B100 ») defines the range of cells in column B (adjust as needed).
3. Comparison Loop:
   • The first loop For Each cellA In rangeA iterates through each cell in column A.
   • The second loop For Each cellB In rangeB checks if the value of cellA exists in any of the cells in column B.
   • If a match is found, the matchFound flag is set to True, and the inner loop exits using Exit For.
4. Handling Matches and Discrepancies:
   • If a match is found, cellA.Interior.Color = RGB(144, 238, 144) colors the cell in light green.
   • If no match is found, cellA.Interior.Color = RGB(255, 99, 71) colors the cell in light red.
5. Completion Message:
   • A message box appears when the reconciliation process is complete, indicating that the task has finished.

Customization:

• Data Ranges: Adjust the ranges A2:A100 and B2:B100 as needed depending on your dataset size.
• Colors: You can customize the colors for matches and non-matches by changing the RGB values in the code.
• Larger Ranges: If you have more data, simply adjust the ranges for rangeA and rangeB.

Sample Data Table:

After running the code:

• Values in column A that match values in column B will be colored green.
• Values in column A that do not match any values in column B will be colored red.

Possible Enhancements:

1. Add functionality to handle duplicates.
2. Create a summary report that lists the matches and discrepancies.
3. Integrate error handling for empty cells or invalid data.

This automation script will help streamline the process of reconciling data and save time in accounting or financial tasks.