Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I want to apply conditional formatting to change the background color of alternating rows on a spread sheet. The rows will alternate between two colors (yellow and green) two lines each.

This is the code I am using:

=MOD(ROW(),2)*($A1<>"")

and

=(1-MOD(ROW(),2))*($A1<>"")

It changes every other line, but I want every two lines to be the same color. What do I need to change to make that work?

share|improve this question

to save some processor cycles, you can format whole range with the 1st color and use this formula to conditionally format 3rd and 4th row of every 4 rows:

=MOD(ROW()-1;4)>1

if you use this conditional format only to used range, e.g. by creating an Excel Table (the one without alternating row colors), there should not be need to include $A1<>"", but feel free to use it anyway

share|improve this answer

Try this (matches rows 1, 2, 5, 6, etc):

=MOD(FLOOR(ROW()/2+0.5,1),2)*($A1<>"")

and this (matches rows 3, 4, 7, 8, etc):

=MOD(FLOOR(ROW()/2-0.5,1),2)*($A1<>"")
share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .