conditional formatting of cell and adjacent cells

[kristian97]

Hello.

I want to format cell in which is text "Martin" (C10) and also two cells on the left of the cell with "Martin" (A10:B10).
Expectation:

With formula
=C2="Martin"
I am able to format only 2nd cell to the right of cell containing "Martin".

When I change formula to
=A2="Martin"
then cell with Martin is formatted but not 2 cells on the left of it.

I am using "Applies to" range
=$A$2:$I$32

I know how to do it for all 3 cells when I use separate ranges for groups of 3 columns, but I need to use it with only one range.

Sorry, I cannot install XL2BB Add-in :/ Hopefully explanation with pictures will be enough.

Thank you.

 

[etaf]

ignore - wrong

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

See if this is what you want. You would select A2:I32 and apply the Conditional Formatting shown.

23 10 24.xlsm
	A	B	C	D	E	F	G	H	I
1
2	1	a		1	a		1	a
3	2	b		2	b		2	b
4	3	c		3	c		3	c
5	4	d		4	d		4	d
6	5	e		5	e		5	e	Martin
7	6	f		6	f		6	f
8	7	g		7	g		7	g
9	8	h		8	h		8	h
10	9	i	Martin	9	i		9	i
11	10	j		10	j		10	j
12	11	k		11	k		11	k
13	12	l		12	l		12	l
14	13	m		13	m		13	m
15	14	n		14	n		14	n
16	15	o		15	o	Martin	15	o
17	16	p		16	p		16	p
18	17	q		17	q		17	q
19	18	r		18	r		18	r	Martin
20	19	s		19	s		19	s
21	20	t		20	t		20	t
22	21	u		21	u		21	u
23	22	v		22	v		22	v
24	23	w		23	w		23	w
25	24	x		24	x		24	x
26	25	y		25	y		25	y
27	26	z		26	z	Martin	26	z
28	27	aa		27	aa		27	aa
29	28	ab		28	ab		28	ab
30	29	ac		29	ac		29	ac
31	30	ad		30	ad		30	ad
32	31	ae		31	ae		31	ae
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:I32	Expression	=SEARCH("|Martin|","|"&A2&"|"&B2&"|"&C2&"|")	text	NO

or a second (probably simpler) option

23 10 24.xlsm
	A	B	C	D	E	F	G	H	I
1
2	1	a		1	a		1	a
3	2	b		2	b		2	b
4	3	c		3	c		3	c
5	4	d		4	d		4	d
6	5	e		5	e		5	e	Martin
7	6	f		6	f		6	f
8	7	g		7	g		7	g
9	8	h		8	h		8	h
10	9	i	Martin	9	i		9	i
11	10	j		10	j		10	j
12	11	k		11	k		11	k
13	12	l		12	l		12	l
14	13	m		13	m		13	m
15	14	n		14	n		14	n
16	15	o		15	o	Martin	15	o
17	16	p		16	p		16	p
18	17	q		17	q		17	q
19	18	r		18	r		18	r	Martin
20	19	s		19	s		19	s
21	20	t		20	t		20	t
22	21	u		21	u		21	u
23	22	v		22	v		22	v
24	23	w		23	w		23	w
25	24	x		24	x		24	x
26	25	y		25	y		25	y
27	26	z		26	z	Martin	26	z
28	27	aa		27	aa		27	aa
29	28	ab		28	ab		28	ab
30	29	ac		29	ac		29	ac
31	30	ad		30	ad		30	ad
32	31	ae		31	ae		31	ae
CF (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:I32	Expression	=OR(A2="Martin",B2="Martin",C2="Martin")	text	NO

 

[kristian97]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

See if this is what you want. You would select A2:I32 and apply the Conditional Formatting shown.

23 10 24.xlsm
	A	B	C	D	E	F	G	H	I
1
2	1	a		1	a		1	a
3	2	b		2	b		2	b
4	3	c		3	c		3	c
5	4	d		4	d		4	d
6	5	e		5	e		5	e	Martin
7	6	f		6	f		6	f
8	7	g		7	g		7	g
9	8	h		8	h		8	h
10	9	i	Martin	9	i		9	i
11	10	j		10	j		10	j
12	11	k		11	k		11	k
13	12	l		12	l		12	l
14	13	m		13	m		13	m
15	14	n		14	n		14	n
16	15	o		15	o	Martin	15	o
17	16	p		16	p		16	p
18	17	q		17	q		17	q
19	18	r		18	r		18	r	Martin
20	19	s		19	s		19	s
21	20	t		20	t		20	t
22	21	u		21	u		21	u
23	22	v		22	v		22	v
24	23	w		23	w		23	w
25	24	x		24	x		24	x
26	25	y		25	y		25	y
27	26	z		26	z	Martin	26	z
28	27	aa		27	aa		27	aa
29	28	ab		28	ab		28	ab
30	29	ac		29	ac		29	ac
31	30	ad		30	ad		30	ad
32	31	ae		31	ae		31	ae
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:I32	Expression	=SEARCH("|Martin|","|"&A2&"|"&B2&"|"&C2&"|")	text	NO

or a second (probably simpler) option

23 10 24.xlsm
	A	B	C	D	E	F	G	H	I
1
2	1	a		1	a		1	a
3	2	b		2	b		2	b
4	3	c		3	c		3	c
5	4	d		4	d		4	d
6	5	e		5	e		5	e	Martin
7	6	f		6	f		6	f
8	7	g		7	g		7	g
9	8	h		8	h		8	h
10	9	i	Martin	9	i		9	i
11	10	j		10	j		10	j
12	11	k		11	k		11	k
13	12	l		12	l		12	l
14	13	m		13	m		13	m
15	14	n		14	n		14	n
16	15	o		15	o	Martin	15	o
17	16	p		16	p		16	p
18	17	q		17	q		17	q
19	18	r		18	r		18	r	Martin
20	19	s		19	s		19	s
21	20	t		20	t		20	t
22	21	u		21	u		21	u
23	22	v		22	v		22	v
24	23	w		23	w		23	w
25	24	x		24	x		24	x
26	25	y		25	y		25	y
27	26	z		26	z	Martin	26	z
28	27	aa		27	aa		27	aa
29	28	ab		28	ab		28	ab
30	29	ac		29	ac		29	ac
31	30	ad		30	ad		30	ad
32	31	ae		31	ae		31	ae
CF (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:I32	Expression	=OR(A2="Martin",B2="Martin",C2="Martin")	text	NO

Hello Peter, thank you for your reply.
I would like apply it for a lot more columns than in the example so unfortunately

=SEARCH("|Martin|","|"&A2&"|"&B2&"|"&C2&"|")
or
=OR(A2="Martin",B2="Martin",C2="Martin")

would not be feasible formulas to use due to manual input of columns.

How would you suggest to implement it in a dynamic way?

 

[Peter_SSs]

I would like apply it for a lot more columns than in the example so unfortunately

Can you explain that in more detail and/or give examples?

 

[Peter_SSs]

Thanks for updating your version details.

I would like apply it for a lot more columns

Do you mean that there are more than 3 columns in each section? If so, consider this option.

23 10 24.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
2	1	a				1	a				1	a
3	2	b				2	b				2	b
4	3	c				3	c				3	c
5	4	d				4	d				4	d
6	5	e				5	e				5	e			Martin
7	6	f				6	f				6	f
8	7	g				7	g				7	g
9	8	h				8	h				8	h
10	9	i			Martin	9	i				9	i
11	10	j				10	j				10	j
12	11	k				11	k				11	k
13	12	l				12	l				12	l
14	13	m				13	m				13	m
15	14	n				14	n				14	n
16	15	o				15	o			Martin	15	o
17	16	p				16	p				16	p
18	17	q				17	q				17	q
19	18	r				18	r				18	r			Martin
20	19	s				19	s				19	s
21	20	t				20	t				20	t
22	21	u				21	u				21	u
23	22	v				22	v				22	v
24	23	w				23	w				23	w
25	24	x				24	x				24	x
26	25	y				25	y				25	y
27	26	z				26	z			Martin	26	z
28	27	aa				27	aa				27	aa
29	28	ab				28	ab				28	ab
30	29	ac				29	ac				29	ac
31	30	ad				30	ad				30	ad
32	31	ae				31	ae				31	ae
CF (3)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:O32	Expression	=OR(A2:E2="Martin")	text	NO

 

[kristian97]

Thanks for updating your version details.


Do you mean that there are more than 3 columns in each section? If so, consider this option.

23 10 24.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
2	1	a				1	a				1	a
3	2	b				2	b				2	b
4	3	c				3	c				3	c
5	4	d				4	d				4	d
6	5	e				5	e				5	e			Martin
7	6	f				6	f				6	f
8	7	g				7	g				7	g
9	8	h				8	h				8	h
10	9	i			Martin	9	i				9	i
11	10	j				10	j				10	j
12	11	k				11	k				11	k
13	12	l				12	l				12	l
14	13	m				13	m				13	m
15	14	n				14	n				14	n
16	15	o				15	o			Martin	15	o
17	16	p				16	p				16	p
18	17	q				17	q				17	q
19	18	r				18	r				18	r			Martin
20	19	s				19	s				19	s
21	20	t				20	t				20	t
22	21	u				21	u				21	u
23	22	v				22	v				22	v
24	23	w				23	w				23	w
25	24	x				24	x				24	x
26	25	y				25	y				25	y
27	26	z				26	z			Martin	26	z
28	27	aa				27	aa				27	aa
29	28	ab				28	ab				28	ab
30	29	ac				29	ac				29	ac
31	30	ad				30	ad				30	ad
32	31	ae				31	ae				31	ae
CF (3)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:O32	Expression	=OR(A2:E2="Martin")	text	NO

I mean that I will have multiple groups of 3 columns like ABC, DEF, GHI, JKL, MNO,..., where in the 3rd column (C,F,I,L,O,...) "Martin" can be selected.

However, you have provided me the right solution for this problem, I have updated it as following:

Thank you!