Macro to constantly color every other visible row

Hello all,

I am new to the forum, and I apologize if this has been posted elsewhere. I have a file that I would like all visible rows in a certain range to alternate colors. For example, the first row is white, next is gray, etc. My range is A10:Q25. I have found a few macros and other methods for accomplishing this. The biggest issue that I am having is that the alternating colors don't update when I add, remove, or hide the rows within that range. Hiding seems to be the worst issue. Some macros seem to update when I add/remove rows, but hiding rows always gives me problems. The file is going to be used as a template, so rows will be changed regularly. Can someone suggest a macro that would work within A10:Q25 that would alternate row colors consistently and continuously, even if rows within that range are added, removed, or hidden?

For reference, I have experimented with "format as table", but I need some cells to be merged, which apparently eliminates that option. I also have attempted some conditional formatting, but I haven't found an equation that updates consistently. I'm guessing that a macro would be the best option, but I'm open to anything that works. Honestly, having the row colors update as I hide/show the rows would be enough. Any help would be appreciated.

Hi Dan,

Thank you for your reply. That is exactly what I needed. I knew there had to be some way to do this.

Please forgive my inexperience with VBA, but I have a follow up question regarding your post. I noticed that the alternate row coloring starts at row 10, but continues infinitely down the sheet. Is there a way to alter the code to make the coloring fall within a certain range (or ranges)? Perhaps it starts at row 10 and ends at row 30, for example? And is it possible to have the coloring start up again after a few rows?

I will see what I can do to eliminate the merged cells. I have only been merging cells along a row (horizontally) and never between rows (vertically). I don't know if that makes a difference or not.

Thanks again, I appreciate your help.

This worked perfectly. I noticed it seemed to process faster when the rows were limited, which was an added benefit. Thank you again for your assistance. Out of all of the ways that I have tried to alternate row colors, this seems to be the most reliable method.

This code has been working very well for me so far. I am wondering if someone can help with one last hurdle I am experiencing. I would like to find a way to get this code to stop and start in different places as I move down the sheet. I have been experimenting with changing the range, but I think the complexity of the code is beyond my abilities. I would like the alternating row colors to start at a specific location (the first row under the header in "Table 2" in the attached image), and continue down to the last row of table 2. It would skip past the header of table 3, and start up again until it reached the bottom of table 3. The process would repeat for each table (4,5,6,etc.) until it got to the bottom of the last table.

The main issue is that the number of rows in each table are not predetermined. People will be adding, deleting, and hiding rows in each table section. Sometimes the user may hide a whole table section completely (Table 3 for example).

If anyone has any thoughts on how to make something like this work, I would greatly appreciate it.Excel Screen Example.JPG

After some experimenting, I altered the formula that Dan originally suggested in post #3. I set aside columns to the right of the page (#26-39 in this case, all not in the print area) and designated a whole column for each stop and start point. I would put one line of text in each column on the row that I wanted the formatting to start and stop. Using the xlUp option, I would have the code identify the marked rows to set my range. The code was just repeated over and over with different ranges set. I added in text to deal with errors, in case entire ranges were hidden, it would just skip down to the next section of code. So far it seems to be working really well. It was probably more complicated than it needed to be, but I don't care as long as it works. Thank you all for your assistance. I have pasted my code below in case it might help others get started.

Please Login or Register  to view this content.

One unexpected quality that I didn't think about is that having the code automatically update eliminates my undo option. I'm thinking of changing the way that it works, maybe having the rows update each time I save instead of after every click. Since saving affects the whole workbook instead of just the current sheet, I'm assuming there will be more steps involved. It might be a topic for another day.

Hi Dan,

This post is pretty old but it's very close to what I am trying to do. Same concept where the sheet will update the formatting to every other considering the rows that get hidden. However if I wanted the loop to keep looking until it found a blank row, how would that look? The code you provided has a specific number of rows, how could I make it so the loop counts and fixes formatting up until "empty row" instead of "row 10"