Macro to give every other section a gray color for easier viewing

I created a copy of a pivot table because I needed it to be editable but the entire background is white. I need some help in getting a macro to make the background alternate between Gray and White based on the Vial sections(A,1, A,10....) So that the Vial name row and the Samples within it and all the data alternate between Gray and White so that it will be easier to view each section.These files can be over 10,000 rows long and hundreds of columns wide so it will really help a lot.

Thanks

.
.
Here's one way to accomplish your goal :

Please Login or Register  to view this content.

Paste the above macro into a Routine Module. You can activate it by a command button on the worksheet.
Click the small arrow in the upper left corner of the worksheet. Doing so highlights all rows on the worksheet.
Then click the command button.

The code is not working. I tried to debug it to see if I can figure out where it goes wrong and I think it may have to do with it not finding the end of the counter. It goes through the for loop only 1 time and exits the sub only coloring 1 cell not the row. Also it only colors the cell where you are actively on. So if the curser is on A1 then only A1 is colored, if the curser is on H12 then only H12 is colored. I need the coloring to start at A3 and go across to the end of the table for that section (Vial & Sample Name with Data). Could the "Selection.Rows.Count" not be properly formatted to get the end number? Please advise....

Thanks!

.
.


Click the small arrow in the upper left corner of the worksheet. Doing so highlights all rows on the worksheet.

Then click the command button.

Alternatively, you can highlight only the area you want to apply the line coloring to, then click the button.

Oh OK, I did that but the coloring is still not correct. I have attached 2 files, one has the outcome from your code and the other file has what I am looking for. Please let me know if this is be possible changing parts of your code?

Thanks,

My apologies for not responding sooner. I thought I had ... but it turns out my answer was posted to another thread.

I any case, here is the answer to your question:

Please Login or Register  to view this content.

See attached :

Wow, that might of been a problem......Sorry for the delay, now it's working good. I have one question, how can I generalize the range so that it will auto set it counting the rows and columns? I never know how long the file will be so I would like it to calculate itself.

Thanks so much for your help.