2

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

The only problem is, that I want the conditional formatting to only apply to the cells that have character entered in them. The cells with no characters will remain white. As the user enters data in the cells, the backgrounds will appear making a kind of bar graph of colored backgrounds. I can make the conditional formatting work on all the cells by using:

=MOD(ROW()-3,1*2)>0

and

=MOD(ROW()-2,1*2)>0

But, I don't know how to make it apply to only the cells with data in them.

2 Answers 2

2

Try using these 2

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

and

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

5
  • I tried this and it does make the rows alternating colors. However, they are shaded all the time. I need them to only be shaded if there is an entry in the cell. If there is no entry, then they should not be shaded. May 21, 2012 at 22:16
  • I was assuming that you either want the whole row shaded....or nothing. Using the formulas I suggested will format each row as long as column A is populated for that row - if column A isn't populated the row won't be shaded. If you specifically want each cell shaded only if that specific cell is populated then remove the $ signs. Note: in either case I assume you have a range that starts at A1. If it starts somewhere else then adjust accordingly May 21, 2012 at 22:23
  • In the original code that I provided what would I change to make every two rows different colors. I want 2 rows blue, 2 rows gray, two rows blue, two rows gray..etc. May 21, 2012 at 23:45
  • @barryhoudini Is there a cleaner method than the following for detecting any cell in my row is non-empty? =MOD(ROW(),2)*OR($A15<>"",$B15<>"",$C15<>"",$D15<>"",$E15<>"",$F15<>"")
    – Harvey
    Mar 23, 2013 at 4:24
  • Asking that question led me to the correct google search to come up with this: =MOD(ROW(),2)*(COUNTBLANK($A15:$F15)<>COLUMNS($A15:$F15))
    – Harvey
    Mar 23, 2013 at 4:29
1

These should work:

=AND(NOT(ISBLANK(A1)),MOD(ROW()-3,1*2)>0)
=AND(NOT(ISBLANK(A1)),MOD(ROW()-2,1*2)>0)

You must log in to answer this question.

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