Formatting to color code unique differing values?

Lauren Y

HI - looking to see if I can alternate color coding of rows using conditional formatting to make it easier to read. For example, the first set of items (sample size) would be gray, then the next white then staffing training back to gray. They are variable numbers and rows can be added to a group at any point

Paul Newcome

Try this:

Insert an auto-number column (called "Auto" in this example). You can have whatever formatting you want/need.

Next insert a text/number column (called "Number" in this example) with the following column formula:

=MATCH(Auto@row, Auto:Auto, 0)

Then you would insert another text/number column (called "Formatting" in this example) with this column formula:

=IF(ISODD(COUNT(DISTINCT(COLLECT([Task Name]:[Task Name], Number:Number, @cell <= Number@row)))), "Gray", "White")

And finally you would set up conditional formatting rules based on this final "Formatting" column.

Paul Newcome

Try this:

Insert an auto-number column (called "Auto" in this example). You can have whatever formatting you want/need.

Next insert a text/number column (called "Number" in this example) with the following column formula:

=MATCH(Auto@row, Auto:Auto, 0)

Then you would insert another text/number column (called "Formatting" in this example) with this column formula:

=IF(ISODD(COUNT(DISTINCT(COLLECT([Task Name]:[Task Name], Number:Number, @cell <= Number@row)))), "Gray", "White")

And finally you would set up conditional formatting rules based on this final "Formatting" column.

Lauren Y

Genius!! Thank you so much Paul. Appreciate all you do for this community.

Paul Newcome

Happy to help. 👍️