Conditional Formatting - Change text color based on calculation

[jfeagle]

I have sales results, that we red/yellow/green based on how close they are to their target goals each month. What is the trick to using conditional formatting so I don't have to update the text color manually every month? I have found the results for based on color coding like what I have in column D but not what I do. So right now it would be the June line we'd be using. And really I'd only need to change it if it's red or green (like under 67% would be red, over 100% would be green). Thanks in advance!

 

[DanteAmor]

So right now it would be the June line we'd be using

To know which month to use, I recommend that you write the month in cell E1 exactly as you have written in column N

Try:

Dante Amor
	A	B	C	D	E	F							M	N	O	P	Q
1		Goal	Actual	% to Goal	Jun.									Jul.
2				138%	l									Aug.
3				70%	l									Sep.
4				47%	l									Oct.
5				80%	l									Nov.
6														Dec.	<33%		>50%
7														Jan.
8														Feb.
9														Mar.
10														Apr.
11														May.	<61%		>92%
12														Jun.	<67%		>100%
Hoja3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:E5	Expression	=D2>SUBSTITUTE(VLOOKUP($E$1,N:Q,4,0),">","")+0	text	NO
E2:E5	Expression	=D2<SUBSTITUTE(VLOOKUP($E$1,N:O,2,0),"<","")+0	text	NO
E2:E5	Cell Value	<>""""""	text	NO

The conditional formatting I used is for the text, it will look like this:

---------------
Or you can use the format to fill the cell:

Dante Amor
	A	B	C	D	E	F							M	N	O	P	Q
1		Goal	Actual	% to Goal	Jun.									Jul.
2				138%										Aug.
3				70%										Sep.
4				47%										Oct.
5				80%										Nov.
6														Dec.	<33%		>50%
7														Jan.
8														Feb.
9														Mar.
10														Apr.
11														May.	<61%		>92%
12														Jun.	<67%		>100%
Hoja3 (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:E5	Expression	=D2>SUBSTITUTE((VLOOKUP($E$1,N:Q,4,0),">","")+0	text	NO
E2:E5	Expression	=D2<SUBSTITUTE((VLOOKUP($E$1,N:O,2,0),"<","")+0	text	NO

 

[jfeagle]

To know which month to use, I recommend that you write the month in cell E1 exactly as you have written in column N

Try:

Dante Amor
	A	B	C	D	E	F							M	N	O	P	Q
1		Goal	Actual	% to Goal	Jun.									Jul.
2				138%	l									Aug.
3				70%	l									Sep.
4				47%	l									Oct.
5				80%	l									Nov.
6														Dec.	<33%		>50%
7														Jan.
8														Feb.
9														Mar.
10														Apr.
11														May.	<61%		>92%
12														Jun.	<67%		>100%
Hoja3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:E5	Expression	=D2>SUBSTITUTE(VLOOKUP($E$1,N:Q,4,0),">","")+0	text	NO
E2:E5	Expression	=D2<SUBSTITUTE(VLOOKUP($E$1,N:O,2,0),"<","")+0	text	NO
E2:E5	Cell Value	<>""""""	text	NO

The conditional formatting I used is for the text, it will look like this:
View attachment 96091

---------------
Or you can use the format to fill the cell:

Dante Amor
	A	B	C	D	E	F							M	N	O	P	Q
1		Goal	Actual	% to Goal	Jun.									Jul.
2				138%										Aug.
3				70%										Sep.
4				47%										Oct.
5				80%										Nov.
6														Dec.	<33%		>50%
7														Jan.
8														Feb.
9														Mar.
10														Apr.
11														May.	<61%		>92%
12														Jun.	<67%		>100%
Hoja3 (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:E5	Expression	=D2>SUBSTITUTE((VLOOKUP($E$1,N:Q,4,0),">","")+0	text	NO
E2:E5	Expression	=D2<SUBSTITUTE((VLOOKUP($E$1,N:O,2,0),"<","")+0	text	NO

Thank you! I will test this out. I appreciate your quick response. After 2 years I'm quite over doing this manually so hoping this will help.

 

[jfeagle]

To know which month to use, I recommend that you write the month in cell E1 exactly as you have written in column N

Try:

Dante Amor
	A	B	C	D	E	F							M	N	O	P	Q
1		Goal	Actual	% to Goal	Jun.									Jul.
2				138%	l									Aug.
3				70%	l									Sep.
4				47%	l									Oct.
5				80%	l									Nov.
6														Dec.	<33%		>50%
7														Jan.
8														Feb.
9														Mar.
10														Apr.
11														May.	<61%		>92%
12														Jun.	<67%		>100%
Hoja3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:E5	Expression	=D2>SUBSTITUTE(VLOOKUP($E$1,N:Q,4,0),">","")+0	text	NO
E2:E5	Expression	=D2<SUBSTITUTE(VLOOKUP($E$1,N:O,2,0),"<","")+0	text	NO
E2:E5	Cell Value	<>""""""	text	NO

The conditional formatting I used is for the text, it will look like this:
View attachment 96091

---------------
Or you can use the format to fill the cell:

Dante Amor
	A	B	C	D	E	F							M	N	O	P	Q
1		Goal	Actual	% to Goal	Jun.									Jul.
2				138%										Aug.
3				70%										Sep.
4				47%										Oct.
5				80%										Nov.
6														Dec.	<33%		>50%
7														Jan.
8														Feb.
9														Mar.
10														Apr.
11														May.	<61%		>92%
12														Jun.	<67%		>100%
Hoja3 (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:E5	Expression	=D2>SUBSTITUTE((VLOOKUP($E$1,N:Q,4,0),">","")+0	text	NO
E2:E5	Expression	=D2<SUBSTITUTE((VLOOKUP($E$1,N:O,2,0),"<","")+0	text	NO

This worked perfectly, thank you so much!!