Alternate Highlighting Rows of Duplicate Part Numbers

[cbritton796]

At a loss........
Trying to highlight multiple rows to gray where there is a set of duplicate part numbers then let the next part number(s) stay white in an effort to make it easier to read. I can't combine the rows because each line is a different location.The report is imported from a Warehouse system,(Sheet1) which has empty row between part numbers.
I even copied it over to Sheet 2 and sorted to get rid of blanks. The report is to long for Conditional formatting this example is just a small part of the file. We run this regularly and takes hours for someone to do. The closest I've gotten is getting every other row to highlight???? the column I'm keying or using is "M".

Any help would be appreciated!

[6StringJazzer]

Welcome to the Forum!

Why is it too big for conditional formatting? Conditional formatting is the textbook method of doing what you need, and actually the only method I can think of without writing VBA to do it.

No idea what this means: "the column I'm keying or using is 'M'." Do you mean the part number is in column M? How many columns are you using?

It will be much easier to understand your problem if you provide your file. This allows us to see your data, layout, code (if there is any), much easier than describing it in several paragraphs. You still have to explain what you want, but it makes the whole process easier. If you are looking for formulas to produce a desired result, it helps if you create a mock-up of what you want the result to look like. And once we understand the problem, having your file will let us experiment with your data, formulas, and code, and possibly attach a file with a completed solution. If the file is too large to attach, it might compress enough if you zip it.

To post a file:

1. Under the text box where you type your reply click the Go Advanced button.
2. In the next screen look above the reply box and click the "paper clip" icon. You will get a pop-up screen.
3. In the pop-up, click Add then Select to select a file from your local drive
4. Click the Upload button to upload the file
5. Click Done to attach it.

It will be displayed as an attachment underneath your post text. Alternatively as the last step you can click the Insert Inline button and a hyperlink to the file will be inserted directly into your post text in the spot where your cursor is set at the moment you click the button.

[cbritton796]

At a loss........
Trying to highlight multiple rows to gray where there is a set of duplicate part numbers then let the next part number(s) stay white in an effort to make it easier to read. I can't combine the rows because each line is a different location.The report is imported from a Warehouse system,(Sheet1) which has empty row between part numbers.
I even copied it over to Sheet 2 and sorted to get rid of blanks. The report is to long for Conditional formatting this example is just a small part of the file. We run this regularly and takes hours for someone to do. The closest I've gotten is getting every other row to highlight???? the column I'm keying or using is "M".

Any help would be appreciated!

[cbritton796]

Thanks had to change my settings to add attachment. M is the column Im using for data to highlight. Im trying to do it in VBA so we can use it on several different reorts by just changing a few values

[6StringJazzer]

I am not sure why you don't want to use CF. I have removed your manual shading and added CF on Sheet1 as an example.

Also, the behavior of this file is strange. It's not big at all. And there is not a single formula. But response time for removing the existing shading was almost forever.

[cbritton796]

Thanks for your help!!

[6StringJazzer]

On re-reading your OP and your PM to me I see that I was focusing on the "duplicate" part of your description. In fact, duplicates are not the issue. You simply want shading that alternates between consecutive groups of part numbers. Also possible with CF, though I couldn't figure out how to do it without a helper column. I added a column in AQ. See attached revision below.

By the way I didn't notice the first time that you have a blank row in between every data row, and the blank rows are set to a height of 0.75. Any particular reason to do that? It makes everything else more complicated. You also have lots of merged cells, which aren't really an issue with the shading but can cause other problems.

Also, here is a VBA solution (no helper column required) that would go in the code module for the sheet needing the shading:

Please Login or Register  to view this content.