Highlighting duplicated values

[kmakjop]

I have been at this for a few weeks now and i cant seem to figure out a way to highlight all the duplicated values in column "A" then trigger a message to advise the user of the duplicated data. I didnt want to use conditional formatting but because of my reocurring failure in trying to achieve this I m now reluctantly using the conditional function to assist. However while it works well to highlight the duplicated record, I cant figure out a way to make it trigger a warning message to the user.

Can someone assist with having the records that are duplicated highlited and to trigger a warning message, please. Without the message the user wont know why the records are highlighted.

[jeffreybrown]

For this you would have to use VBA. Is this what you want to do?

[kmakjop]

Yes I would prefer using VBA, initially that was my intent but doing it was just too hard for me and searching the net was giving me any helpful results; hence me reluctantly using CF. Can you suggest or assist me with something that will work?

[jeffreybrown]

Try...

Please Login or Register  to view this content.

[kmakjop]

Thank you so much for your suggestion; however I still can’t get this code to work how I intend it to. For example: whether there are duplicates or not, the duplicate error message is being thrown. I also wanted it to validate from cell A: 8 until it discovers an empty cell in column “A”. Can someone kindly modify this code for me please, thank you.

[jeffreybrown]

Please attach a sample file with your expected results.

[kmakjop]

i am trying to attach the file but its giving me some problems, think its more my computer or the connection i am using. In the interim, could you help me modify the code to just highlight cells in the "A" column that are duplicated? I would like it to do so until it detects an empty cell in the column.

your code works well with the exception of the fact that it triggers the msgbox for dupes even when there is nothing highlighted. I would like the duplication check to start at cell: 8, column "A". Each time i try modifying the code it throws an error. Cant understand why something i thought would be so simple is pressuring my thoughts so much.

HELP PLEASE!!!!

[jeffreybrown]

Here is the code modified to start at row 8, but other than this I'm not sure what you need to see that isn't happening.

Please look at this sample file and tell me what should happen?

Attach a sample workbook.

When you are in Advanced reply, click on the paperclip to open the upload window.

View Pic

[kmakjop]

Thank you so much for assisting, it seems to be working much better so far. However i am getting an error with the screenupdating = true (the last line). Is there also anyway to have the application run in the "A" column until it gets to an empty cell then stops. Because its taking way to long to complete the execution because i think its evaluating all the cells in column "A".

Thanks again for assisting me.

[jeffreybrown]

How about you try...

Please Login or Register  to view this content.

[kmakjop]

thank you so much jeffrey brown, your code worked very well. It takes very long to execute; its the best when i have gotten from anyone. thank you again. is there anysuggestions on how to make it go faster? i only populated about 7 cells and it took very long to execute, so i am thinking it ran the code through the entire column. Can it stop as soon as it encounters an empty cell. If that will be too much work though, I will just satisfy with what you have already done for me.

[jeffreybrown]

Even with only 7 cells it took a long time to execute...!

What else do you have going on in that workbook?

How long does it take for this attached example to run?

[kmakjop]

your example executes within a second (basically instantly)

[kmakjop]

what i noticed though jeffrey is that your code is written in the module, i placed mine in the sheet. would that affect the performance of the application?

[jeffreybrown]

Why did you place your macro in the sheet code?

Did you try to run it outside the sheet in a normal module where it should be unless you are running a worksheet change event?

If the attachment I posted runs instantaneously, then why not in yours?

Could it be something else is in your worksheet like heavy formulas keeping the processor tied up?

[kmakjop]

i am going to try again to attach the file so you can advise me

[kmakjop]

the file apparently is too large to upload to upload. If i place the code in the module how do u call it using the command click? I noticed with yours you used the form control not the activex control. But i normally use the activex, is there a pros and cons associated with using one over the other? And as earlier asked, how do i call the function using the activex control button click.

[jeffreybrown]

No, I actually just used a shape (Insert tab >> Illustrations >> Shapes >> pick a shape)

After you put the shape on the sheet, right click, Assign macro

The difference between the form control and the activex control is mostly preference. I just go with the easiest and that is the shape.

ActiveX Control
Developer tab >> Controls >> Insert >> activex control command button

Right click >> view code >> paste the code inside the the Sub >> End Sub

Please Login or Register  to view this content.

Forms Control
For the Forms control, as soon as you place it on the spreadsheet the Assign macro dialogue box will appear.

[kmakjop]

ok thats how i normally assign the code when i use the activex. But doing it this way wouldnt be calling it from the module but more running it from the sheet. What i was wondering is if i code embed the code in the module and call it when the button is clicked. Rather than placing the code itself in the sheet under the actual button command click

[jeffreybrown]

I'm stepping out the door right now so don't have time to test it, but I would say the code for the activex is run from the sheet as that is where the VBA puts it. Maybe not another option but from the sheet.

[kmakjop]

ok cool thanks