Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

Try using these 2

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

and

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

share|improve this answer
    
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. –  Gary Whitehead May 21 '12 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 –  barry houdini May 21 '12 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. –  Gary Whitehead May 21 '12 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 '13 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 '13 at 4:29
add comment

These should work:

=AND(NOT(ISBLANK(A1)),MOD(ROW()-3,1*2)>0)
=AND(NOT(ISBLANK(A1)),MOD(ROW()-2,1*2)>0)
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.