Forum Discussion
Highlighting every 10th row
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?
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. 🙂
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:
=NOT(MOD(ROW(A1)-$B$1,10))
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?
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. 🙂
