Conditional formatting question

Question

I have a large dataset I need to review (1000+ rows). Each row contains about a dozen columns, one of which is the name of an individual. I need to check the information for each individual, and I'd like to use conditional formatting to help me do this by making all rows for each individual a color that's distinctive from the rows above and the rows below. Below there are so many individuals (several hundred), I cannot create a conditional formatting formula in which I manually enter the values for formatting.

Here is how I would like this to look:

In this example, I've used alternating colors, but a range of colors is fine. What matters is that color change will enable me to more easily tell the difference between each person.

Answer 1

Select A2:L1200 or so (include more columns and rows if needed).

The active cell in the selection should be in row 2.

In the following I'll assume that the name is in column A.

On the Home tab, click Conditional Formatting > New Rule.

In the Style box, click Classic.

Under the Classic box, select 'Use a formula to determine which cells to format'.

Enter the formula

=ISODD(COUNTA(UNIQUE($A$2:$A2)))

On the Format with box, click custom format.

In the Format Cells dialog box, activate the Fill tab.

Select a color.

Click OK until the dialog boxes are closed.

Answer 2

Unfortunately, this did not work. It only formatted a few rows, and not in the pattern I need. Let me provide an example here of how I want this to look. Perhaps this will help.

Rows 1-3 (not counting the header row) should have a red fill. Rows 4-7 should have a green fill. Rows 8-9 should have a red fill. And so on. the goal is to use the alternating fill color to mark when the categories change. The formula you provided did not work, either for Excel on my Apple desktop or online with Office 365. Is there another way to do this?

Thanks.

Heading	Category 1	Subcategory1
1	Fruit	Apples
1	Fruit	Apples
1	Fruit	Oranges
1	Fruit	Strawberry
2	Meat	Beef
2	Meat	Chicken
2	Meat	Pork
2	Meat	Fish
1	Grain	Rice
1	Grain	Corn

Answer 3

Like this? I used column B as the "identifier" column this time.

Sample workbook: AlternatingColor.xlsx

Answer 4

Hi,

Using this formula, you will have to write different rules because there are different colours

=COUNTA(UNIQUE($B$2:$B2))

So you will have to check that if the result is 1, then colour to Green, if it is 2, then red etc.