VBA for finding duplicated rows of data with changes

[Abgar]

Hi all,

Hoping someone can help with a quick VBA code (or point me in the right direction).

I have a data worksheet, which contains many rows of data, spreading across multiple columns. Each day, a new extract of data is added to the spreadsheet (added as new rows at the bottom), and there will be duplicated data.
We have been using conditional formatting on Column A (a unique ID number) to show the duplicates, and then we delete the duplicates.
When adding the new data, sometimes the Column A will be the same, but there will be a different value in Column H (based on changes in the system we extract the data from).

I'm hoping to essentially have a VBA code that will run through each row and check if ALL data for the row in columns A-H is the same. If it is all the same, highlight the duplicated row of data in Fill Colour RED
If Column A is the same, but there is any difference in data from Column B-H, then highlight the row in Fill Colour Green.
And if Column A is unique (not duplicated), then don't highlight that row at all.

Per the below example, the top 8 rows are the original data, then the new data is added below. Rows 11, 13 and 15 are highlighted green, becuase the ID in column A is a duplicate, but some of the other data is different.
Rows 12 and 14 are highlighted red becuase Column A is a duplicate, but all the other data is the same.
Columns 16-18 are not highlighted, becuase Column A is a new unique value, and is not duplicated.

Book1
	A	B	C	D	E	F	G	H
1	Unique ID	Surname	First name	Client	State	Category	Contact	Date
2	302383	Slater	Tom	Bills Boats	Tasmania		Lockyer	17/03/2021
3	297842	Smith	Peter	Bills Boats	Queensland		Lockyer	17/03/2021
4	302097	Temple	Paul	Toms Tomatos	New South Wales	Biggest	Lockyer	17/03/2021
5	302272	Rough	Mark	Betteys Beer	Tasmania		Trident	17/03/2021
6	299573	Grout	John	Bills Boats	Tasmania	Baddest	Trident	17/03/2021
7	302900	Wilson	Luke	Toms Tomatos	Queensland		Goodall	17/03/2021
8	301106	Smith	Matthew	Bills Boats	New South Wales		Peters	17/03/2021
9	302163	Bleeker	Sally	Betteys Beer	Tasmania		Smith	17/03/2021
10
11	302383	Slater	Tom	Bills Boats	Tasmania		Lockyer	18/03/2021
12	297842	Smith	Peter	Bills Boats	Queensland		Lockyer	17/03/2021
13	302097	Temple	Paul	Toms Tomatos	New South Wales	Biggest	Smith	17/03/2021
14	302272	Rough	Mark	Betteys Beer	Tasmania		Trident	17/03/2021
15	299573	Grout	John	Toms Tomatos	Tasmania	Baddest	Trident	17/03/2021
16	123456	Wilson	Luke	Toms Tomatos	Queensland		Goodall	17/03/2021
17	654321	Smith	Matthew	Bills Boats	New South Wales		Peters	17/03/2021
18	987645	Bleeker	Sally	Betteys Beer	Tasmania		Smith	17/03/2021
Sheet1

Thanks in advance for any info you can share.

Cheers,
ABGar

 

[bigMe]

dear Zot,
i just found your post, and it's exactly what i need, but instead highlight the duplicate row with red color, i prefer to add note "Duplicate"in column I, and can you help me to modified your code? thank you.

best regards,
bigMe