Change cell colour, based on matched cells in sheet 2

[Bobbo Jones]

Hi all, Is this possible?

I have a sheet with ten thousand+ rows of data. I have a 2nd sheet where I enter new data every week, which comes from the 1st sheet. I generally add 15 rows at a time on the 2nd sheet.

The 2nd sheet will sometimes have duplicates, or multiple occurances of the same row.

What I want, is a visual cue on sheet 1, that lets me know how many times I have copied data from them onto the 2nd sheet

If I have never used row 432, I want it to have no cell colour.
If I have used row 433 once, I want it to be yellow.
If I have used row 432 twice I want it to be green
etc up to about 6 times.

Now another complication of this is that the match will not be the entire row, but just 1,2 or 3 cells in said rows. The other cells in the row will be changing when entered into sheet 2.

Is this even possible??

thanks for taking the time to read this!
Bobbo

[Bobbo Jones]

I am thinking using the DCOUNT function, in a new column on sheet 1, and then conditional formating in that cell that would change the row colour. Im not sure how to create that formula though

[Bobbo Jones]

I ended up using this function
=COUNTIF('Old Comps'!M:M,Pallette!M2)

but it is not surviving a sort.
Old comps is sheet 2, pallette is sheet 1.

Does anyone have any advice?
Cheers