Copying Cell problem and code effiecency issue

Hello all ,

I wrote this code to highlight quadruplets "red" then highlighting triplicates "Yellow" and last duplicates "orange" .. all of that using the FormatConditions function .

then I have a function that changes the FormatConditions.interior.color into cell.interior.clor ..so I can search the cells based on their highlight and extract the data from the selection to column AA if they were quadruplets ...and extract them to collumn AC if triplicates ...and to AE if they were dupliates ...(there is a problem in the code here that it doesn`t get to the point of calculating the duplicates to extract them , in other words I gt data on AA and AC but there is none on AE even though there are duplicates in the data )


then I get the code to check if there are any duplicates in the resulting columns (AA , AC ans AE) ..another problem here is that the check is only done on AA not the rest ..I attached a sample file with the macro ...

any help will be appreciated

thank you

NB : the code takes almost a couple of minutes to calculate

See if this does what you want:

Please Login or Register  to view this content.

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

Hello romperstomper ,

Thank you for reply ...


I admit the code you typed is much much cleaner and faster than mine ...but it gives me this message
"Run-time error '451': Property let procedure not defined and property get procedure did not return an object"...

and when I choose Debug .. I found that this line is highlighted in the code ( the red select line )

Please Login or Register  to view this content.

Sorry - I should have retested after I converted to late bound code. I suggest you set a reference in the VBEditor (tools-references) to the Microsoft Scripting Runtime library and then change this line:

Please Login or Register  to view this content.

to this:

Please Login or Register  to view this content.

Do you only want exactly 4,3 and 2 repetitions highlighted then?
There shouldn't be any duplicates in AA, AC, and AE using that code. Can you give me an example?

Originally Posted by romperstomper

Do you only want exactly 4,3 and 2 repetitions highlighted then?
There shouldn't be any duplicates in AA, AC, and AE using that code. Can you give me an example?

yes ..only 2,3 and 4 ...I attached the sheet after applying conditional formatting on AA, AC and AE highlighting the duplicates .

thank you for the fast reply

Small tweak should do it:

Please Login or Register  to view this content.

I've added comments to this version - hopefully they are self-explanatory, but post if you need clarification:

Please Login or Register  to view this content.