Row coloring in frozen columns

Quality idea.

Here is a similar way that requires only one extra column and does not have the problem with blank cells.

Formula in D2 =IF(ISODD(ROW()),CHAR(10000),"")

fill down to complete the column

Create the highlighting rule for all the cells in the header column(s):

1. Select all the cells in the header column(s) except those in header rows and footer rows. For my table it is A2:A22.
2. Start making a conditional highlighting rule
3. Use the rule "Text ends with"
4. In the box where you put what it ends with, click the green oval
5. Click on cell D2
6. Add a $ so it is $D2
7. Click on the green checkmark to accept it
8. Choose "custom style". If you have an odd number of header rows, make the background the lighter of the two alternating colors. If an even number then make it the darker of the two.
9. Click Done

Format the same cells of column A (A2:A22 for my table) to whatever the other alternating color should be.

Hide the column with the formulas.

CHAR(10000) is a little pencil icon. I chose it because it is unlikely to be found in a spreadsheet, which is a requirement for this method to work.

You can do it manually by setting the cell background of every other row. It would be nice if header columns (frozen or not) took on the alternating color automatically but they do not.

Hi

Here is one way to make it automatic. Here is a table with Alternating Row Colour (I chose "Silver" from the Colour Wheel). Two Header Columns (columns A and B). Headers are filled with "Mercury" from the Colour Wheel.

Add extra columns on the right (columns E and F) as "helpers". You can hide these extra columns when all is working well.

Formula in E2: IF(ISODD(ROW()),A2,"")

Fill right to F2 and fill down to the bottom of the table.

That will copy the contents of the Header Columns if the row number is odd.

Conditional Highlighting. Select the header cells as so:

One note, if the Header Columns do not contain entries, this method does the opposite of what you want.

But if the Header Columns are blank, that makes no sense!

Regards,

Ian.

Hi BD,

Excellent idea. CHAR(10000) to the rescue again!

Regards,

Ian.