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.

Answer 2

You are going to want to setup two Conditional Formatting rules based upon a count unique type of formula like the following.

=MOD(INT(SUMPRODUCT(1/COUNTIF($C$2:$C2, $C$2:$C2))), 2)
=NOT(MOD(INT(SUMPRODUCT(1/COUNTIF($C$2:$C2, $C$2:$C2))), 2))

The problem is that you do not want to extend that down with full column references since it uses the SUMPRODUCT function to provide array (or cyclic) type processing. Keeping track of the range it covers if rows and/or columns are added or removed becomes a problem.

The solution is to have a sub procedure at the ready that can quickly create or renew the two CF rules for you based upon the Range.CurrentRegion property. That is the 'island' of data extending out until it meets a fully blank row or column.

Sub Set_CF_rules()

    With Worksheets("Sheet1")
        With .Cells(1, 1).CurrentRegion
            With .Resize(.Rows.Count - 1, .Columns.Count).Offset(1, 0)
                .FormatConditions.Delete

                .FormatConditions.Add Type:=xlExpression, Formula1:= _
                    "=MOD(INT(SUMPRODUCT(1/COUNTIF($C$2:$C2, $C$2:$C2))), 2)"
                With .FormatConditions(.FormatConditions.Count).Interior
                    .PatternColorIndex = xlAutomatic
                    .ThemeColor = xlThemeColorAccent6
                    .TintAndShade = -0.249946592608417
                    .Parent.StopIfTrue = True
                End With

                .FormatConditions.Add Type:=xlExpression, Formula1:= _
                    "=NOT(MOD(INT(SUMPRODUCT(1/COUNTIF($C$2:$C2, $C$2:$C2))), 2))"
                With .FormatConditions(.FormatConditions.Count).Interior
                    .PatternColorIndex = xlAutomatic
                    .ThemeColor = xlThemeColorAccent6
                    .TintAndShade = 0.799981688894314
                    .Parent.StopIfTrue = True
                End With

            End With
        End With
    End With

End Sub

I've set a dark red and a pale red for the highlight 'striping' Experiment with the macro recorder while filling cells with varying colors, shades and patterns until you find something you want and replace the appropriate values in the above code.