none
Excel 2010 Alternating Row Colors, How do I Set Starting Row?

    Question

  • Found this:     =mod(row(),2)=1

    Here:  http://www.techonthenet.com/excel/questions/cond_format2.php

    I can follow the instructions easy enough (unless 2010 has a catch).  How do I set this to start at row 27?  Tried    =mod(row(27), 2) = 1    didn't work at all.

    Anybody done this before?


    May the fleas of a thousand camels feast happily on the lower regions of your enemies. And may their arms be too short to scratch!

    Thursday, March 14, 2013 8:23 PM

Answers

  • If you have already set conditional formatting, remove it first.

    Then, instead of selecting the entire worksheet, select the range from row 27 down as far as you want.

    Now set up conditional formatting with the formula =MOD(ROW(),2)=1.


    Regards, Hans Vogelaar

    • Marked as answer by psifreak Friday, March 15, 2013 12:27 PM
    Thursday, March 14, 2013 9:37 PM
  • Hey psifreak,

    Just hopping in with a bit of unsolicited...

    My personal fave for a table is to conditional format just a bottom border line using =MOD(ROW()-ROW($A$xx),3)=0  [Where Axx is the row of the table header].  For me, it has much better visual appeal than the "Venetian Blinds" of alternating colors.  If you do prefer color bands, you can go really old school back to 'green bar' printer paper by combining your fill tint with =MOD(ROW(),6)<3.

    The eye can quite easily follow a row of data in the upper, lower, or middle of a group of three lines.  More so, I think, than the single line alternation standard with Table Styles.

    For your consideration.

    Mike

    • Marked as answer by psifreak Monday, March 18, 2013 12:22 PM
    Friday, March 15, 2013 1:18 PM

All replies

  • If you have already set conditional formatting, remove it first.

    Then, instead of selecting the entire worksheet, select the range from row 27 down as far as you want.

    Now set up conditional formatting with the formula =MOD(ROW(),2)=1.


    Regards, Hans Vogelaar

    • Marked as answer by psifreak Friday, March 15, 2013 12:27 PM
    Thursday, March 14, 2013 9:37 PM
  • OH......*facepalm*.....overthinking again I am I am.  Thanks yet again Hans!

    May the fleas of a thousand camels feast happily on the lower regions of your enemies. And may their arms be too short to scratch!

    Friday, March 15, 2013 12:26 PM
  • Hey psifreak,

    Just hopping in with a bit of unsolicited...

    My personal fave for a table is to conditional format just a bottom border line using =MOD(ROW()-ROW($A$xx),3)=0  [Where Axx is the row of the table header].  For me, it has much better visual appeal than the "Venetian Blinds" of alternating colors.  If you do prefer color bands, you can go really old school back to 'green bar' printer paper by combining your fill tint with =MOD(ROW(),6)<3.

    The eye can quite easily follow a row of data in the upper, lower, or middle of a group of three lines.  More so, I think, than the single line alternation standard with Table Styles.

    For your consideration.

    Mike

    • Marked as answer by psifreak Monday, March 18, 2013 12:22 PM
    Friday, March 15, 2013 1:18 PM
  • I'll definitely try that Mike.  I tend to agree with your assessment about the blinds, just not enough of an excel guru to understand most of the formatting code.  Thanks!

    May the fleas of a thousand camels feast happily on the lower regions of your enemies. And may their arms be too short to scratch!

    Monday, March 18, 2013 12:23 PM