2

I want my spreadsheet to automatically generate alternating colors based on row header change, like the image below.

Ideally without creating auxiliary columns or macro/scripts (which I can create myself but don't want to). The length of the table is variable and the table is dynamic.

example

I've tried to create some magic logic with conditional formatting, but while I can create logic that identifies a row header changes (using the =(A3=A2) logic, I could not find a way to identify to color of the previous row. So the answer to the question on how to (using formulas and no script) identify the background color of a determined cell could be helpful as well.

Improve this question

2 Answers 2

Reset to default
2

Here is a solution using formulas and no scripts: it does require two helper columns the second of which you can easily apply the conditional formatting of the colors of your choosing:

Helper Column 1 (A1:A10=A2:A11 should be replaced with the cells you are looking for change)

=arrayformula({n(whatthefoxsay());IF(A1:A10=A2:A11,n(whatthefoxsay()),1)})

Helper Column 2 (replace C1:C11 with the helper column 1 and voila the fox will make your conditional formatting very simple)

=lambda(fox,arrayformula(mod(MMULT(IF(ROW(fox)>=TRANSPOSE(ROW(fox))=TRUE,1,0),fox),2)))(C1:C11)

I wasn't able to accomplish this without helper columns, I came up with a few solutions that used circular reference and other horrible ideas but the performance was so bad it wasn't even worth mentioning.

Improve this answer
5
  • Fantastic, thank you! I've also learned about google sheets easter eggs that I was not aware of!
    – Oren
    Oct 21, 2022 at 19:54
  • btw, i've slightly extended the formulas to =arrayformula({n(whatthefoxsay());IF(A1=A2:A,n(whatthefoxsay()),1)}) and =lambda(fox,arrayformula(mod(MMULT(IF(ROW(fox)>=TRANSPOSE(ROW(fox))=TRUE,1,0),fox),2)))(C1:C) so that the solution also works when I populate the table with more data
    – Oren
    Oct 21, 2022 at 19:56
  • I am assuming also that I can't make all items of the first column except the first appearance transparent, because only one conditional formatting rule can apply to a cell, right?
    – Oren
    Oct 21, 2022 at 19:58
  • @Oren cells can have multiple conditional formats but the one you order to the top is executed last.
    – CodeCamper
    Oct 21, 2022 at 21:37
  • @Oren you can use array_constrain to make it so it doesn't overflow with your new reference.
    – CodeCamper
    Oct 21, 2022 at 21:40
2

You'll need two formulas (without creating auxiliary columns or macro/scripts):

for the green color:

=isodd(code($A1))

for the yellow color:

=iseven(code($A1))

cf

Improve this answer
4
  • Thanks, that's ingenious! But unfortunately the 'A, B, Cs' were just examples, and the actual categories are not in any particular "code()" order
    – Oren
    Oct 21, 2022 at 19:49
  • If you want to have correct answers, please provide us with correct data
    – Daniele
    Oct 21, 2022 at 20:21
  • @Daniele I'll give you a thumbs up anyway, definitely a great idea.
    – CodeCamper
    Oct 21, 2022 at 21:35
  • @CodeCamper thank you
    – Daniele
    Oct 22, 2022 at 7:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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