Current Macro Highlights Duplicate Records and Whites them out, but is over-highlighting

[TheCroatianSensation]

Hello Everyone!

I am using the macro below (in a string of multiple other macros) to format a report. One worksheet on this report has a listing of excess items from multiple locations. What this macro initially did (and still does) was highlight the duplicate items (by item number) and change the text to white for easier reading. The problem is that the macro has begun to highlight items that are not duplicates and it is causing a good deal of manual work. I inherited this macro, and I am having some trouble debugging it. Any help would be very much appreciated.

Thank you!
TCS

Please Login or Register  to view this content.

[patel45]

I need a sample file with few rows but many duplicates for testing

[arlu1201]

Do you have a sample file that you can upload which you used along with this code?

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[TheCroatianSensation]

Patel,

Thank you for getting to me so quickly!

Attached is an example of the tab after all of the other macros are run on it. Sorry that I could not include more information.... The only thing that is really needed is Sim Number anyway.

Thanks,
TCS

[patel45]

You attached a file without macro, the code you posted does not work

[HaHoBe]

Hi, TCS,

your macro can be simplified and amended like this (I wonīt compare the row value to the row before but to the entire column):

Please Login or Register  to view this content.

Ciao,
Holger

[TheCroatianSensation]

Holger,


Thank you so much for your help. I still have more testing to do, but so far it looks like the simplification worked. Thank you so much, you are THE MAN!!!

I will update the thread as "solved" after I finish 2 or 3 more rounds of testing.


Thanks,
TCS

[TheCroatianSensation]

Upon further review, the macro did fix some of the mistakes on the smaller reports, but the larger reports began to have mistakes with the new code. It seems that the errors usually happen when there is a long run of duplicates and the code just whites out everything skipping over 2 or 3 lines that should have been left alone.

Any further help on this issue would be greatly appreciated, and thank you to Holger for your code.

Thanks,
TCS

[HaHoBe]

Hi, TCS,

could you please attach a sample for that (maybe only with the column with the sim numbers) for a check? The code should not only compare the data to the row ahead but to the whole range from Row1 to the active row and write down the number of items found - no need for the data to be sorted first.

Ciao,
Holger

[TheCroatianSensation]

Attached is the sample that you requested. Thank you!

[HaHoBe]

Hi, TCS,

when I look into the workbook and insert the formula into Column V every line with 1 is still in black while all others are in white. Maybe I just donīt get the point of which lines were to be visible.

Ciao,
Holger

[TheCroatianSensation]

Holger,

The first line of every new "Sim Item" should be black, while all of the others should be white. The following lines in the "PO Alerts Sample" should be black and not white: 122, 127, 134, 187, 205, 209, 323, 324.

I am sorry that I did not explain this more clearly.

Thanks,
Tony

[HaHoBe]

Hi, Tony,

row 122 shows 00281 which is found in Rows 2 to 6 and 122 to 126. The differences are in Columns O and R.
row 127 at 8 to 14 and 127 to 133, difference in Column R.

So we need to take Column R into our formula as one more criteria?

Ciao,
Holger

[TheCroatianSensation]

Holger,

My mistake on that one. I actually think the extra criteria would be on column c - Branch Plant.

Sorry about that.

Thanks,
Tony

[HaHoBe]

Hi, Tony,

letīs take Sim Nr. 00466: columns A to I are empty in the example and thus would lead to same result as in the macro already on hand.

What about trying to run the simplified macro with the formula that you had created in your original macro and look at the results then? Originally the rows were compared one by one while I changed it (without knowing too much about the workbook) to look at every entry made.

Ciao,
Holger

[TheCroatianSensation]

I was not comfortable pasting a bunch of information on to the thread and that is why those columns are empty.

The original code was missing highlights.

How would I be able to include looking at column C as well as column V?

Thanks

[TheCroatianSensation]

Could we sort using column R then?

[HaHoBe]

Hi, Toy,

using two columns: insert a column into Column V and concatenate teh contents of both columns wanted, the do the normal check but to the newly created column as reference (instead of Column N), formula for that could be

Please Login or Register  to view this content.

Same would go for the use of Column R.

Ciao,
Holger

[TheCroatianSensation]

I am trying to concatenate V and C into the column but I can't seem to get it to work correctly.

[HaHoBe]

Hi, Tony,

please have a look at the attached workbook. Concatenate is on N, C and R.

Code used:

Please Login or Register  to view this content.

Ciao,
Holger

[TheCroatianSensation]

Holger,

It highlights the top row (column names) whenever there are no highlighted rows in the sheet.

Thanks,
Tony

[TheCroatianSensation]

Holger,

Thank you very much for helping me out with this. You are the MAING!!!

Thanks,
TCS