Using conditional formatting to alternate the formatting of data by category

Question

I have a spreadsheet with a bunch of rows like this:

Name    |   ID    |   Category  |    Attributes...
--------------------------------------------------------
name0   |   753   |   cat1      |    ...
name1   |   724   |   cat1      |    ...
name2   |   149   |   cat1      |    ...
name3   |   265   |   cat1      |    ...
name4   |   032   |   cat2      |    ...
name5   |   991   |   cat2      |    ...
name6   |   238   |   cat2      |    ...
name7   |   005   |   cat3      |    ...
name8   |   632   |   cat3      |    ...
name9   |   393   |   cat3      |    ...

And I would like to format it so it's a little bit easier to distinguish the rows of separate categories. I'd therefore like to alternate shading and not shading based on the values in the Category column. In other words, in the example above, I would like for the rows with cat1 to be dark, then cat2 to be light, then cat3 to be dark again all the way down the sheet.

Is this possible?

Answer 1

Please select ColumnsA:D and HOME > Styles - Conditional Formatting, New Rule..., Use a formula to determine which cells to format and Format values where this formula is true::

=ISEVEN(SUMPRODUCT(1/COUNTIF($C$1:$C1,$C$1:$C1)))

Format..., select your choice of formatting (dark?), OK, OK.

For 'light' I have assumed that either no fill is light enough or, if not, you apply standard fill to suit (CF, where triggered, will override it).

The COUNTIF is a sequential counter that starts at 1 for each category. Dividing that into 1 and applying SUMPRODUCT generates a sequential counter by category (only changes when the category does). ISEVEN is applied to pick alternate categories. To investigate the detailed workings of formulae M$ has provided Evaluate Formula.