How can one alternate row colors in a SHARED WORKBOOK in Excel 2013?

Copper Contributor

...

 

Excel Folks, 

 

Please help.

 

How can one alternate row colors in a SHARED WORKBOOK in Excel 2013?

 

Please advise.

 

Note this is a SHARED WORKBOOK so one cannot have an Excel Table.

 

Note Excel says "This workbook cannot be shared because it contains Excel tables" so Excel Tables are out when using a Shared Workbook.

 

What do you think?

 

How can one alternate row colors in a SHARED WORKBOOK in Excel 2013?

 

Please advise.

 

Thanks.

 

-- Mark Kamoski

 

...

 

5 Replies

@mkamoski 

 

Hi Mark,

 

You may apply conditional formatting rule to your range with formula

=MOD(ROW(),2)=1

 

...

Sergei,

I appreciate your reply, but that did not work for me, sad news for me.

Perhaps I need more detail?

In Excel 2013, in my Shared Workbook, in Home, Styles, the "Conditional Formatting" is grayed-out.

I did see online here...

https://support.office.com/en-us/article/about-the-shared-workbook-feature-49b833c0-873b-48d8-8bf2-c...

...that the "Adding or changing conditional formats" is part of the "unsupported items" for a shared workbook so maybe there is another way?

As of now, I am still blocked.

Please LMK what you think, etc.

Thanks.

-- Mark Kamoski

...

@mkamoski 

 

Oops, sorry, I forgot conditional formatting doesn't work on shared workbooks. As workaround you may add helper column with the similar formula, apply filter to your range together with this helper column, filter rows with 1 (or zero) in it, apply color to filtered rows, remove filter

image.png

 

Sergei,

Ok, that works a workaround, I suppose.

However, it seems like a manual re-touch would be necessary after adding rows, unless I am understanding you incorrectly relative to the "filter rows... apply color... remove filter" part.

Thanks.

-- Mark Kamoski

@mkamoski 

 

Mark - yes, after inserting / adding rows manual adjustment will be required. If only for adding you may color your range with some gap. Or color another empty sheet as much as needed and apply format from it by Format Painter when the range changes. Another variant is write some macro.