Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Shading Rows 10
- Thread starter pctechguy
- Start date
-
7
- #2
Do CTRL+A, then Format / Conditional Formatting / Change 'cell value is' to 'formula is' and put in =MOD(ROW(),2)=1
Now click on the format button and choose a nice pastel colour from the patterns tab. Hit OK till you are out and you are done.
You could also put in
=MOD(ROW(),2)=0 if you wanted the other rows to be coloured.
You could also put in
=MOD(ROW(),3)=0 if you wanted every 3rd row to be coloured.
You could also put in
=MOD(ROW(),4)=0 if you wanted every 4th row to be coloured.
You could also put in
=MOD(ROW(),5)=0 if you wanted every 5th row to be coloured.
But you probably get the drift by now:-
Regards
Ken.............
Now click on the format button and choose a nice pastel colour from the patterns tab. Hit OK till you are out and you are done.
You could also put in
=MOD(ROW(),2)=0 if you wanted the other rows to be coloured.
You could also put in
=MOD(ROW(),3)=0 if you wanted every 3rd row to be coloured.
You could also put in
=MOD(ROW(),4)=0 if you wanted every 4th row to be coloured.
You could also put in
=MOD(ROW(),5)=0 if you wanted every 5th row to be coloured.
But you probably get the drift by now:-
Regards
Ken.............
dianemarie
Instructor
COOL!!! I love it. Ken, could you explain the MOD function? I've looked it up in Help, I kind of get it, but I'm not seeing what it's doing here. Thank you.
- Thread starter
- #4
=MOD(ROW(),2)=1
=mod(number,divisor) says take a number, divide it by another number and then leave me the remainder as a whole number. So for example, 5 divided by 2 is not 2.5 but is indeed 2 remainder 1. 6 divided by 2 is 3 remainder 0. 7 divided by 2 is 3 remainder 1 and so on.
The ROW() bit will simply use the Row number as the number for the MOD function. Whatever the Row number is it will be divided by 2 and the result will always be either 1 or 0. The conditional formatting simply plays on that to say OK, if the result is = 1 then colour the rows, hence every other row gets coloured. Even if you delete or add rows it doesn't matter. because the ROW() function will reflect whatever number the row happens to be.
Regards
Ken.............
=mod(number,divisor) says take a number, divide it by another number and then leave me the remainder as a whole number. So for example, 5 divided by 2 is not 2.5 but is indeed 2 remainder 1. 6 divided by 2 is 3 remainder 0. 7 divided by 2 is 3 remainder 1 and so on.
The ROW() bit will simply use the Row number as the number for the MOD function. Whatever the Row number is it will be divided by 2 and the result will always be either 1 or 0. The conditional formatting simply plays on that to say OK, if the result is = 1 then colour the rows, hence every other row gets coloured. Even if you delete or add rows it doesn't matter. because the ROW() function will reflect whatever number the row happens to be.
Regards
Ken.............
dianemarie
Instructor
Very clear. Thank you Ken, the example along with your explanation was excellent training.
-
3
- #9
![[smile]](/data/assets/smilies/smile.gif "[smile] [smile]")
) effect, you can use:Thanks Ken. I "borrowed" (okay - stole) your star worthy method and combined it with a method I use for another program.
Here's another way to do it. (for those who don't want to play with conditional formatting)
Use the "Fill Color" to high light the row(s) you want to alternate.
Highlight the rows, plus the same number of unhighlighted
row. If you're doing every other row highlight row one and then pick rows 1 and 2.
Click on the "Format Painter" (it's the paintbrush next to the Undo button.
Press Ctrl+A (Select All) and bam! your entire sheet has the alternating rows.
You would have to do this method before any other formatting is done to avoid the possiblity of formatting a date as a number.
Mike
Here's another way to do it. (for those who don't want to play with conditional formatting)
Use the "Fill Color" to high light the row(s) you want to alternate.
Highlight the rows, plus the same number of unhighlighted
row. If you're doing every other row highlight row one and then pick rows 1 and 2.
Click on the "Format Painter" (it's the paintbrush next to the Undo button.
Press Ctrl+A (Select All) and bam! your entire sheet has the alternating rows.
You would have to do this method before any other formatting is done to avoid the possiblity of formatting a date as a number.
Mike
- Status
