I have a report listing sales orders, or more specifically, each line of a sales order. I want to alternately color each sales order so there’s a nice visual separation, but I don’t know how many lines each sales order will have. If my sales order numbers were animal names and I was color blind, it might look like this:
The conditional formatting for this color banding is shown below.
The formula uses a David Hagar formula from Chip Pearson’s site to determine the number of unique entries in the list. This has the effect of numbering the unique entries. The MOD function determines if it’s even or odd and only odd entries are formatted.
This shows how the formula evaluates out. All the formulas in the worksheet are entered as array formulas (Control+Shift+Enter), but the CF formula is entered normally. The formulas shown are from line 8, if you couldn’t tell.