Macro to Compare columns in two excel files and paste the few columns in 2nd sheet for selected records.

'Macro to search for new project ids and change in current phase of a existing project - mark the 'changes in new sheet. Paste this macro in the new sheet.

 ' Copy this macro in the sheet "FCCT Project List 10-9_QC Compliance and QC Project status updates_10-22.xlsm" and run the macro, make sure two files are open while running.

Sub newprojectsearchandcurrentphase()

Dim i, j, L, N, LN, flag, i1, j1

 ' A1 is to store the values of column A(Project id) from the old sheet

Dim A1

' E is to store the value of column E(Master Project id) from the old sheet

Dim E

 ' Merging A1 and E values

Dim A1E

' A2 is to store the values of column A(Project id) from the new sheet

Dim A2

' F is to store the value of column F(Master Project id) from the new sheet

Dim F

 ' Merging A2 and F values

Dim A2F

 

 

 'new sheet

file1 = "FCCT Project List_10-24.xlsx"

Workbooks(file1).Sheets(1).Activate

ActiveWorkbook.ResetColors

 

For i = 2 To 571

 A1 = Workbooks("FCCT Project List_10-24.xlsx").Sheets(1).Cells(i, 1).Value

 F = Workbooks("FCCT Project List_10-24.xlsx").Sheets(1).Cells(i, 6).Value

 flag = 0

A1F = A1 & F

 'old sheet

Workbooks("FCCT Project List 10-9_QC Compliance and QC Project status updates_10-22.xlsm").Sheets(1).Activate

 

 For j = 12 To 579

 A2 = Workbooks("FCCT Project List 10-9_QC Compliance and QC Project status updates_10-22.xlsm").Sheets(1).Cells(j, 1).Value

 E = Workbooks("FCCT Project List 10-9_QC Compliance and QC Project status updates_10-22.xlsm").Sheets(1).Cells(j, 5).Value

 A2E = A2 & E

  'Check if project id & Master id matches with the records.

 If (A2E = A1F) Then

 flag = 1

  ' Current phase of the project in the new sheet is saved in i1

 i1 = Workbooks("FCCT Project List_10-24.xlsx").Sheets(1).Cells(i, 9).Value

  ' Current phase of the project in the old sheet is saved in j1

 j1 = Workbooks("FCCT Project List 10-9_QC Compliance and QC Project status updates_10-22.xlsm").Sheets(1).Cells(j, 10).Value

 

  ' Copy the content of Release, Req, Test Plan, Test Lab and Defects from old sheet and place in new sheet

 Set shtToCopy = Workbooks("FCCT Project List 10-9_QC Compliance and QC Project status updates_10-22.xlsm").Sheets(1).Range("R" & j & ":Y" & j)

 shtToCopy.Copy Workbooks("FCCT Project List_10-24.xlsx").Sheets(1).Range("R" & i & ":Y" & i)

 

  'Mark the change in current phase for the projects that match

 If (i1 <> j1) Then

  Workbooks("FCCT Project List_10-24.xlsx").Sheets(1).Cells(i, 9).Interior.ColorIndex = 10

 End If

 Exit For

 Else

 flag = 2

 End If

 Next

 

 'Coloring the cells that does not have matching in the old report.

If flag = 2 Then

Workbooks("FCCT Project List_10-24.xlsx").Sheets(1).Activate

'Sheets(1).Cells(i, 1).ColorIndex = 3

Sheets(1).Cells(i, 1).Interior.ColorIndex = 10

End If

Next

 

End Sub