Macro to compare to worksheets and highlight new data

[kcj784]

I'm trying to create a macro that compares two excel worksheets and highlights the rows that are different. Basically, there is a sheet with rows of information. The first 3 columns contain various text that unique to that row of data. There could be a unique identifier for a row in all 3 columns or only one. The rest of the rows contain various info. Each day a report is run generating another sheet. Sometimes there are only one or two new rows of information. Let's say worksheet1 is the older sheet. I want to use search worksheet2 to see if it contains unique identifiers found in worksheet1. If it is found, move on to the next row. If it isn't found, that means the row of data is new and should be highlighted. Am I overlooking a simple way to do this? Thanks.

 

[pplstuff]

Use application.match() on your primary key (unique key). Then use ifs to determine how to handle the cases.

On error resume next
For i = firstrow to lastrow
j = 0
j = application.match(sheets("sheet1").cells(i, 1).value), sheets("sheet2").columns(1), 0)
if j = 0 then _
sheets("Sheet1").cells(i, 1).interior.colorindex = 4
next j


Something like that would get you started, to look at each cell in sheet1, check sheet2 for a match, if there's no match color sheet1 cell red.