Alternating Colors For Excel Based on Cell Value

[austinandrei]

Hi,
Can it be possible to have a quick way to have an alternating color pattern in excel rows depending on the cell value?
Say I have these examples from Cell A1 to A9 with A1 as the header:

THIS IS THE HEADER IN CELL A1

10000123 <tbody> </tbody>

10000123 <tbody> </tbody>

27298951-000001 <tbody> </tbody>

30004240 <tbody> </tbody>

30004240 <tbody> </tbody>

30004240 <tbody> </tbody>

50000495 <tbody> </tbody>

50000495 <tbody> </tbody>

<tbody>
</tbody>

Then Cell A2 to A3 will have the 1st color pattern then Cell A4 will have the 2nd color pattern then Cells A5 to Cell A7 will have again the 1st color pattern then Cell A8 to A9 will have the 2nd color pattern, thus alternating colors for duplicate cell values in Column A. Data will be sorted first as always, thus duplicate values will always stick together to have the same color pattern.

Thanks in advance for the help!

 

[Peter_SSs]

I usually do it like this. Use a vacant column to fill
- The row 1 cell manually with a 1
- Row 2 formula copied down as far as you might need then hide this formula column.

Select A2 to the bottom right of the range you might want coloured & apply the Conditional Formatting formulas shown.

Excel Workbook
	A	B
1	Hdr	1
2	a	-1
3	a	-1
4	a	-1
5	a	-1
6	b	1
7	b	1
8	c	-1
9	c	-1
10	c	-1
11	d	1
12	e	-1
13	f	1
14	g	-1
15	g	-1
16	g	-1
17		1
18		1
19	h	-1
20	h	-1
21
22
Cond Format In Groups

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2	1. / Formula is =$B2=-1	Abc
A2	2. / Formula is =$B2=1	Abc

 

[austinandrei]

Hi Peter,
That was really a way! Thanks for sharing this.

 

[Peter_SSs]

Hi Peter,
That was really a way! Thanks for sharing this.

You're welcome.

 

[blbat]

Peter,

I too would like to thank you for your post- your method works perfectly for me too.

just a curiosity question; IS there a way to do this without a helper column? I imagine it would be quite convoluted if so....

regards,

blbat

 

[mikerickson]

Try with the CF formula
=MOD(SUMPRODUCT(IFERROR(1/COUNTIF(A$1:A1,A$1:A1), 0)),2)=0

 

[Peter_SSs]

Peter,

I too would like to thank you for your post- your method works perfectly for me too.

just a curiosity question; IS there a way to do this without a helper column? I imagine it would be quite convoluted if so....

regards,

blbat

You're welcome.

Yes, you can do this without the helper but it will be considerably more resource-hungry

Excel Workbook
	A
1	Hdr
2	a
3	a
4	a
5	a
6	b
7	b
8	c
9	c
10	c
11	d
12	e
13	f
14	g
15	g
16	g
17
18
19	h
20	h
CF in Groups

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2	1. / Formula is =MOD(SUMPRODUCT(--($A$1:$A1<>$A$2:$A2)),2)=1	Abc
A2	2. / Formula is =MOD(SUMPRODUCT(--($A$1:$A1<>$A$2:$A2)),2)=0	Abc

 

[blbat]

Very Nice Peter...

I know what the Doulbe Unary Operator does, but have never become adept at it's use.

 

[Peter_SSs]

You're welcome

(The mikerickson suggestion did not work for me)