Hello All, i am very new to VBA but i am working my way through, slowly. The code below, NOT MINE, works great between 2 different sheets within the same workbook. Can the code be applied to work between 2 workbooks? I have tried but i am getting to the point where i believe it is not designed for the purpose i have in mind. Any help would be very much appreciated.
Code
Sub compare_cols()
Dim report1 As Workbook 'CSV
Dim report2 As Workbook 'master doc
Set report1 = Workbooks.Open(Filename:="file path") 'csv
Set report2 = Workbooks.Open(Filename:="file path") 'master doc
Windows("report2").Activate
Windows("report1").Activate
Dim myRng As Range
Dim lastCell As Long
'Get the last row
Dim lastRow As Integer
lastRow = ActiveSheet.UsedRange.Rows.Count
'Debug.Print "Last Row is " & lastRow
Dim c As Range
Dim d As Range
Application.ScreenUpdating = False
For Each c In report1.Range("A2:A" & lastRow).Cells
For Each d In report2.Range("A2:A" & lastRow).Cells
c.Interior.Color = RGB(255, 199, 206) 'Light red background color
c.Font.Color = RGB(0, 0, 0) 'Black font color
If (InStr(1, d, c, 1) > 0) Then
c.Interior.ColorIndex = xlNone 'No fill color
Exit For
End If
Next
Next
For Each c In report2.Range("A2:A" & lastRow).Cells
For Each d In report1.Range("A2:A" & lastRow).Cells
c.Interior.Color = RGB(255, 199, 206) 'Light red background color
c.Font.Color = RGB(0, 0, 0) 'Black font color
If (InStr(1, d, c, 1) > 0) Then
c.Interior.ColorIndex = xlNone 'No fill color
Exit For
End If
Next
Next
Application.ScreenUpdating = True
End Sub
Display More