title: Macro to compare two worksheets and highlight where a change has occured
body: <p>I would like to create a macro within a workbook that can be used as a comparison tool.</p>
  
  <p>Historical data will be added to Worksheet 1 'Historical'.  Then current data will be added to Worksheet 2 'New'.  The data is in exactly the same format.</p>
  
  <p>The macro should look down column G in worksheet 1 (which is a key identifier) and also column O (which shows a status).  Then this data should be compared to column G and O in worksheet 2.  </p>
  
  <p>If column G is a match but column O has changed then the entire row, from Worksheet 2 'New', should be pasted into Worksheet 3 'Results'.  </p>
  
  <p>Example;</p>
  
  <p>Worksheet 1 'Historical' - Column G, 123456789 and Column O, Not Valid</p>
  
  <p>Worksheet 2 'New' - Column G, 123456789 and Column O, Valid</p>
  
  <p>As there is a match in column G but the status has changed, the row from Worksheet 2 will be pasted into the next free row in Worksheet 3 'Results'</p>
  
  <p>Any help would be greatly appreciated.  I have played around with adding Vlookup and Countif into the macro without much success.</p>