How to alternate hue of rows in an Excel (or Google Sheets) spreadsheet where every column is a different color?

Question

I've got a bit of an oddball question, and I looked pretty hard to find an answer to no success so apologies in advance if this has already been answered.

Anyways, I have an excel spreadsheet that has every column manually set to a specific color. Now I want to alternate the hues between rows while maintaining the manually-set color. So for example, let's say column A is red and column B is blue. I would want A1 to be dark-red, B1 to be dark-blue. A2 would be light-red, B2 would be light-blue. And so on and so forth alternating until the end of the sheet.

I've thought about sampling the previous cells color and setting the next cells color based on that, but I don't know of any functions that would get me there.

I've looked at macros, and while I can sample a cell's color I cannot figure out how to set it.

I'm currently building the sheet in excel and would like to import it to google sheets when I am complete, so having the spreadsheet formulas or macros be compatible would be great. That being said, I can keep it in excel if I have to so that isn't a must.

Any help would be much appreciated!

Answer 1

you need to set up 4 conditional formatting rules with:

• =ISODD(ROW())
• =ISEVEN(ROW())

Also, I would suggest you to check this addon with which you can control colors easily just by hex code (#ffda56)
https://chrome.google.com/webstore/detail/team-color

TeamColor is a Google Spreadsheet add-on for color management. Type a color code in Hex into a cell. Its background color will change to that color code. This is very useful for sharing color palette with teammates in developing apps, websites and anything else colorful.

Answer 2

You can do it with conditional formatting by selecting the column and then use a custom formula conditional format and enter =isodd(row()) then select your dark hue. Add another conditonal format and enter =iseven(row()) and select your lighter colour.

This method breaksdown when using a filter but you can create a column to count visible rows and then base your conditional formatting formula on that column. Bit more complicated but useful if you need to filter.

Answer 3

Another Sheets option is to apply Alternating colours... (under Format) but this would mean setting the two (or three if including a header of a different hue) colours separately for each column.