Shading Rows 10

[pctechguy]

Does anyone know how to shade every other row in Excel? It needs to be where if you sort the data....the shading stays the same.

 

[KenWright]

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

 

[dianemarie]

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.

 

[pctechguy]

Thanks....that worked great!

 

[hangmeup]

Great! It works for columns too!

 

[KenWright]

=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.............

 

[dianemarie]

Very clear. Thank you Ken, the example along with your explanation was excellent training.

 

[KenWright]

My pleasure Diane - Glad it helped.

Regards
Ken.............

 

[mbarron]

Extra stuff nobody asked for....

If you want to create a green bar (or any other color ) effect, you can use:

For every three rows:
=MOD(ROW()-1,6)<3

For other number of shadings, just make the red number twice the blue number

Mike

 

[KenWright]

I like that - Nice twist - Have a Star on me.

Regards
Ken............

 

[mbarron]

Thanks Ken. I &quot;borrowed&quot; (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 &quot;Fill Color&quot; 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 &quot;Format Painter&quot; (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