+ Reply to Thread
Results 1 to 8 of 8

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

  1. #1
    Registered User
    Join Date
    10-01-2012
    Location
    New Jersey
    MS-Off Ver
    Excel 2010
    Posts
    64

    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
    Attached Files Attached Files

  2. #2
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,731

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

    .
    .
    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.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    10-01-2012
    Location
    New Jersey
    MS-Off Ver
    Excel 2010
    Posts
    64

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

    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!

  4. #4
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,731

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

    .
    .


    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.

  5. #5
    Registered User
    Join Date
    10-01-2012
    Location
    New Jersey
    MS-Off Ver
    Excel 2010
    Posts
    64

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

    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,

  6. #6
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,731

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

    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 :
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

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

    Another:

    Please Login or Register  to view this content.
    Last edited by JOHN H. DAVIS; 05-08-2017 at 12:06 PM.

  8. #8
    Registered User
    Join Date
    10-01-2012
    Location
    New Jersey
    MS-Off Ver
    Excel 2010
    Posts
    64

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

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Macro changing font color to white instead of gray
    By bwmuhich in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-20-2015, 10:40 PM
  2. Macro button to print one color copy and one gray scale.
    By ElmerFud in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-08-2014, 08:16 AM
  3. Replies: 2
    Last Post: 10-20-2012, 09:22 AM
  4. Replies: 0
    Last Post: 04-17-2012, 06:43 AM
  5. find a selection bounded by the color gray
    By Janis in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-13-2006, 05:35 PM
  6. trouble with viewing fill color and font color
    By CMinard in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-12-2006, 04:15 PM
  7. how to get lighter shade of gray for interior color of a cell?
    By Rich in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 03-21-2006, 12:45 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1