VB script to log highlighted changes
Posted on 2015-02-02
I have a vb script in MS Excel 2010 that will compare 2 workbooks and highlight the changes in the new workbook. I would like to alter the script to also log the changes to an external text file. Currently it only highlights the cells yellow, bold and a pop up box indicates the total number of differences.
I would like to log the following information: impacted cell (row and column reference), old value, new value
Below is the script I am using to highlight the changes between workbooks.
My old workbook is: CSG_Mapping_Template.xlsx
My new workbook is: CSG_Mapping_Template_new.xlsx
My sheet that I am comparing is "System Info"
Sub compareSystemInfo() 'and highlight the diffrence
Dim wb1 As Workbook, wb2 As Workbook, sh1 As Worksheet, sh2 As Worksheet
Dim rCount As Long, cCount As Long
Dim myDiffs As Integer
Set wb1 = Workbooks.Open("C:\Import\CSG_Mapping_Template.xlsm")
Set wb2 = Workbooks.Open("C:\Import\CSG_Mapping_Template_New.xlsm")
Set sh1 = wb1.Sheets("System Info")
Set sh2 = wb2.Sheets("System Info")
rCount = sh1.UsedRange.Rows.Count
cCount = sh1.UsedRange.Columns.Count
Dim r As Long, c As Integer
For r = 1 To rCount
For c = 1 To cCount
If Not IsDate(sh2.Cells(r, c)) Then
If sh1.Cells(r, c) <> sh2.Cells(r, c) Then
sh2.Cells(r, c).Interior.ColorIndex = 6
sh2.Cells(r, c).Font.Bold = True
myDiffs = myDiffs + 1
Set sh1 = Nothing
Set sh2 = Nothing
'Display a message box stating the number of differences found
MsgBox myDiffs & " differences found", vbInformation, "System Info Tab"