Hello,
I am trying to automate a rather mundane task of reconciling past data with new datasets. I have attached a sample of what I am trying to do.
Step 1: Take two different ranges as input, past data and new data.
Step 2: Intermediate - What I am doing currently. Sort the data in ascending order w.r.t. Col B because that remains unchanged between the two datasets. Then using If statements to check the two ranges, highlighting changes in yellow and new data in red.
Step 3: Output - Output only the rows with any new data or changes.
I have written the following code so far:
I have also recorded a sorting macro that sorts the two ranges.
Issues where any help would be much appreciated:
1. Combine the sorting with Reconciling so that only one macro needs to be run.
2. My macro consists of hard coded numbers e.g. j+5, the data can have widely varying ranges so I need to figure out how to account for this. There is a blank line between the two ranges.
3. Also I want the new addition to be colored red. This code does not account for new additions but highlights rows in yellow in both ranges leading to a whole empty row highlighted in the past range.
4. Remove rows that have no changes and output the rows that have changes or new data in a new sheet.
Any help would be highly appreciated.
I am trying to automate a rather mundane task of reconciling past data with new datasets. I have attached a sample of what I am trying to do.
Step 1: Take two different ranges as input, past data and new data.
Step 2: Intermediate - What I am doing currently. Sort the data in ascending order w.r.t. Col B because that remains unchanged between the two datasets. Then using If statements to check the two ranges, highlighting changes in yellow and new data in red.
Step 3: Output - Output only the rows with any new data or changes.
I have written the following code so far:
Code:
Sub reconcile()
Dim Report As Worksheet
Dim myRange As Range
Set myRange = Selection
Dim i As Integer, j As Integer
Dim lastRow As Integer
Set Report = Excel.ActiveSheet
lastRow = myRange.Rows.Count
lastCol = myRange.Columns.Count
Application.ScreenUpdating = False
For i = 1 To lastCol
For j = 1 To 5
If InStr(1, myRange.Cells(j, i).Value, myRange.Cells(j + 5, i).Value, vbTextCompare) > 0 Then
Else
myRange.Cells(j, i).Interior.Color = RGB(255, 255, 0)
myRange.Cells(j + 5, i).Interior.Color = RGB(255, 255, 0)
End If
Next j
Next i
Application.ScreenUpdating = True
End Sub
I have also recorded a sorting macro that sorts the two ranges.
Issues where any help would be much appreciated:
1. Combine the sorting with Reconciling so that only one macro needs to be run.
2. My macro consists of hard coded numbers e.g. j+5, the data can have widely varying ranges so I need to figure out how to account for this. There is a blank line between the two ranges.
3. Also I want the new addition to be colored red. This code does not account for new additions but highlights rows in yellow in both ranges leading to a whole empty row highlighted in the past range.
4. Remove rows that have no changes and output the rows that have changes or new data in a new sheet.
Any help would be highly appreciated.