Highlighting every 10th row

New Contributor

I produce a report each month, and I have to select every 10th row with the beginning row varying each month.


This month, I have to select every 10th row that ends in 4--4, 14, 24, etc.


I cannot figure out the Conditional Formatting formula to make this work.


Any suggestions?

4 Replies
there are a few options. You could designate a cell on the sheet or you could create a NAME to define the # and then use the conditional formula:
where A1 is the upper left of the range you defined for the conditional formatting and $B$1 is the cell designated (or use a NAME) for the row to be highlighted.
Thank you for the response. My Excel skills are very basic. I have no clue what you are referring to when you say to create a NAME to define the #. Would you please explain?
best response confirmed by allyreckerman (Microsoft)

@MaryinIdaho1986 Name manager is available under the Formulas menu item:



It is easier to see, set, change and update as a delegated cell (for example you label D1 as "Highlight rows:" and then use E1 as the value to select which 10th row to use.

But in some cases a defined NAME can be useful.  For example if you use it a lot in many formulas then you have a NAME like Rows2Highlight and you don't have to remember what cell $E$1 is.  It is also a way to hide that value so it isn't in some report or as easily or accidentally changed.

So I hope that helps, but I'm guessing you'll be best served just using a particular cell on the sheet.

I got it to work. Here's my formula:


As row 1 is my header, I needed to change my particular cell number to 5. If I have to select every 10th row beginning with 7, my particular cell number will be 8.


Thank you, so much, for your help and patience. It is greatly appreciated. This formula will make my monthly report process much more efficient.


Have a great day!