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:




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

Try using these 2




  • 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

These should work:


Your Answer


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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