Conditional formatting: color rows with with shade when rows have the same value in their respective first cell

[flashgordie]

Is it possible for conditional formatting to evaluate the first cell of a row, compare it to the first cell of the next row, and if =, then look to next row and so on? When they are equal, I would like it to colour the rows the rows that have the same first cell and only the rows that touch each other. In the example below, apple is shaded two different colors.

 

[Peter_SSs]

For the future, you will get faster/better help if you could provide your sample data with XL2BB so we can easily copy for testing.


If you want to have many different colours like that it is more difficult so for a start would colour banding like this be any use to you?

24 01 31.xlsm
	A	B	C
1	Product	Crate	Truck
2	apple
3	apple
4	pear
5	pear
6	mango
7	mango
8	apple
9	banana
10	banana
11	grape
12	lime
13	lime
14	lime
CF Bands

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:C14	Expression	=ISEVEN(SUMPRODUCT(--($A$2:$A2<>$A$1:$A1)))	text	NO
A2:C14	Expression	=ISODD(SUMPRODUCT(--($A$2:$A2<>$A$1:$A1)))	text	NO

 

[Peter_SSs]

You could increase the number of colours before repeat at least a bit more fairly easily. Here I have used 4 colours instead of 2. It is just a matter of copying the formula, pasting into a new rule and changing the final number in the formula.

24 01 31.xlsm
	A	B	C
1	Product	Crate	Truck
2	apple
3	apple
4	pear
5	pear
6	mango
7	mango
8	apple
9	banana
10	banana
11	grape
12	lime
13	lime
14	lime
CF Bands (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:C14	Expression	=MOD(SUMPRODUCT(--($A$2:$A2<>$A$1:$A1)),4)=3	text	NO
A2:C14	Expression	=MOD(SUMPRODUCT(--($A$2:$A2<>$A$1:$A1)),4)=2	text	NO
A2:C14	Expression	=MOD(SUMPRODUCT(--($A$2:$A2<>$A$1:$A1)),4)=1	text	NO
A2:C14	Expression	=MOD(SUMPRODUCT(--($A$2:$A2<>$A$1:$A1)),4)=0	text	NO

 

[flashgordie]

You could increase the number of colours before repeat at least a bit more fairly easily. Here I have used 4 colours instead of 2. It is just a matter of copying the formula, pasting into a new rule and changing the final number in the formula.

24 01 31.xlsm
	A	B	C
1	Product	Crate	Truck
2	apple
3	apple
4	pear
5	pear
6	mango
7	mango
8	apple
9	banana
10	banana
11	grape
12	lime
13	lime
14	lime
CF Bands (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:C14	Expression	=MOD(SUMPRODUCT(--($A$2:$A2<>$A$1:$A1)),4)=3	text	NO
A2:C14	Expression	=MOD(SUMPRODUCT(--($A$2:$A2<>$A$1:$A1)),4)=2	text	NO
A2:C14	Expression	=MOD(SUMPRODUCT(--($A$2:$A2<>$A$1:$A1)),4)=1	text	NO
A2:C14	Expression	=MOD(SUMPRODUCT(--($A$2:$A2<>$A$1:$A1)),4)=0	text	NO

Thank you so much for your reply and yes, I realize now including the xl2bb code makes sense. so with that, please see below. I can certainly work with two colors. And your solution works for the information that I provided, but I should have considered how may data is structured. I am pulling the viewing data that I would like colored from another sheet which holds the source data, so my values are actually spilled formula based on conditions.

Would you have any suggestions for that type of situation? Thank you!!!!

mr excel example.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	I would like the formatting applied to the viewing data however it contains formulas that are pulling from the source data which is on another sheet and pulling it based on a conditions so it viewing data and source data will not always be equal.
2
3	Viewing Data									Source data
4	ID	Product	Crate	Truck						ID	Product	Crate	Truck
5	10001	apple	f	3						10001	apple	f	3
6	10001	apple	e	7						10001	apple	e	7
7	12011	pear	z	5						12011	pear	z	5
8	12011	pear	n	5						12011	pear	n	5
9	12056	Mango	d	3						12056	Mango	d	3
10	12056	Mango	e	2						12056	Mango	e	2
11	12101	Blueberry	a	6						12101	Blueberry	a	6
12	13444	Banana	k	1						13444	Banana	k	1
13	13444	Banana	r	1						13444	Banana	r	1
14	13501	Grape	h	2						13501	Grape	h	2
15	16222	Lime	f	4						16222	Lime	f	4
16	16222	Lime	e	4						16222	Lime	e	4
17	16222	Lime	q	1						16222	Lime	q	1
Sheet2

Cell Formulas
Range	Formula
A5:D17	A5	=J5:M17
Dynamic array formulas.

 

[Peter_SSs]

Thanks for the XL2BB sample data.

I assume that you are saying the spill range could be more or less rows and/or more or less columns that the example shown.
If so, see if this could work for you. Decide on a maximum possible range that the spill could fill & select that to apply the Conditional Formatting to. In my mini sheet below I decided on A5:H25 as the maximum possible range and applied the given CF to that range. This effectively give two-colour banding. If you actually wanted something other than white as the second colour, or more than two colours that could still be achieved.
After applying this CF, try changing the A5 formula to, say =J7:K15

flashgordie.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
3	Viewing Data									Source data
4	ID	Product	Crate	Truck						ID	Product	Crate	Truck
5	10001	apple	f	3						10001	apple	f	3
6	10001	apple	e	7						10001	apple	e	7
7	12011	pear	z	5						12011	pear	z	5
8	12011	pear	n	5						12011	pear	n	5
9	12056	Mango	d	3						12056	Mango	d	3
10	12056	Mango	e	2						12056	Mango	e	2
11	12101	Blueberry	a	6						12101	Blueberry	a	6
12	13444	Banana	k	1						13444	Banana	k	1
13	13444	Banana	r	1						13444	Banana	r	1
14	13501	Grape	h	2						13501	Grape	h	2
15	16222	Lime	f	4						16222	Lime	f	4
16	16222	Lime	e	4						16222	Lime	e	4
17	16222	Lime	q	1						16222	Lime	q	1
18
19
20
21
22
23
24
25
Sheet1

Cell Formulas
Range	Formula
A5:D17	A5	=J5:M17
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A5:H25	Expression	=AND(ROWS($A$5#)>=ROW(A5)-ROW($A$5)+1,COLUMNS($A$5#)>=COLUMN(A5)-COLUMN($A$5)+1,MOD(SUMPRODUCT(--($A$5:$A5<>$A$4:$A4)),2))	text	NO

 

[flashgordie]

Thanks for the XL2BB sample data.

I assume that you are saying the spill range could be more or less rows and/or more or less columns that the example shown.
If so, see if this could work for you. Decide on a maximum possible range that the spill could fill & select that to apply the Conditional Formatting to. In my mini sheet below I decided on A5:H25 as the maximum possible range and applied the given CF to that range. This effectively give two-colour banding. If you actually wanted something other than white as the second colour, or more than two colours that could still be achieved.
After applying this CF, try changing the A5 formula to, say =J7:K15

flashgordie.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
3	Viewing Data									Source data
4	ID	Product	Crate	Truck						ID	Product	Crate	Truck
5	10001	apple	f	3						10001	apple	f	3
6	10001	apple	e	7						10001	apple	e	7
7	12011	pear	z	5						12011	pear	z	5
8	12011	pear	n	5						12011	pear	n	5
9	12056	Mango	d	3						12056	Mango	d	3
10	12056	Mango	e	2						12056	Mango	e	2
11	12101	Blueberry	a	6						12101	Blueberry	a	6
12	13444	Banana	k	1						13444	Banana	k	1
13	13444	Banana	r	1						13444	Banana	r	1
14	13501	Grape	h	2						13501	Grape	h	2
15	16222	Lime	f	4						16222	Lime	f	4
16	16222	Lime	e	4						16222	Lime	e	4
17	16222	Lime	q	1						16222	Lime	q	1
18
19
20
21
22
23
24
25
Sheet1

Cell Formulas
Range	Formula
A5:D17	A5	=J5:M17
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A5:H25	Expression	=AND(ROWS($A$5#)>=ROW(A5)-ROW($A$5)+1,COLUMNS($A$5#)>=COLUMN(A5)-COLUMN($A$5)+1,MOD(SUMPRODUCT(--($A$5:$A5<>$A$4:$A4)),2))	text	NO

Thank you so much Peter_SSs
. Its a piece of art and works beautifully!!! Very much appreciate the help.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.