Sub blah()
With Sheets("Account Definition Mapping")
ADMColumn = Application.Match(Sheets("Data").Range("J1").Value, .Rows(1), 0)
Set DestnTable = .Cells(1, ADMColumn).CurrentRegion
End With
Set DestnTable = Intersect(DestnTable, DestnTable.Offset(1))
Set SceTable = Sheets("Data").Range("A1").CurrentRegion
Set SceTable = Intersect(SceTable, SceTable.Offset(1))
Union(SceTable.Columns(1), SceTable.Columns(3)).Copy DestnTable.Cells(DestnTable.Rows.Count, 1).Offset(1)
DestnTable.Cells(DestnTable.Rows.Count, 1).Offset(1).Resize(SceTable.Rows.Count, 2).Interior.Color = vbYellow
DestnTable.Resize(DestnTable.Rows.Count + SceTable.Rows.Count).RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
End Sub
If I've got the wrong sheet with yellow highlights then:
Sub blah()
With Sheets("Account Definition Mapping")
ADMColumn = Application.Match(Sheets("Data").Range("J1").Value, .Rows(1), 0)
Set DestnTable = .Cells(1, ADMColumn).CurrentRegion
End With
Set DestnTable = Intersect(DestnTable, DestnTable.Offset(1))
Set SceTable = Sheets("Data").Range("A1").CurrentRegion
Set SceTable = Intersect(SceTable, SceTable.Offset(1))
Union(SceTable.Columns(1), SceTable.Columns(3)).Copy DestnTable.Cells(DestnTable.Rows.Count, 1).Offset(1)
Set DBRng = DestnTable.Cells(DestnTable.Rows.Count, 1).Offset(1).Resize(SceTable.Rows.Count, 2)
DestnTable.Resize(DestnTable.Rows.Count + SceTable.Rows.Count).RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
Set NewRowRng = Intersect(DBRng, DBRng.CurrentRegion)
For Each rw In NewRowRng.Rows
ECD = rw.Cells(1).Value
Add = rw.Cells(2).Value
For Each rowe In SceTable.Rows
If rowe.Cells(1).Value = ECD And rowe.Cells(3).Value = Add Then rowe.Interior.Color = vbYellow
Next rowe
Next rw
End Sub