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.
Gary Whitehead
Posted 2012-05-21T18:00:26.927
Reputation: 21
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 – 2012-05-21T22:16:40.693
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 – 2012-05-21T22:23:14.413
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 – 2012-05-21T23:45:27.323
@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 – 2013-03-23T04:24:34.073Asking that question led me to the correct google search to come up with this:
=MOD(ROW(),2)*(COUNTBLANK($A15:$F15)<>COLUMNS($A15:$F15))– Harvey – 2013-03-23T04:29:49.983