Conditional Formatting and IF Statements same cell

[loiacono9]

I am trying to create a formula to alert me when a data point is lower than a value (alert in red), when a value is within a range (alert in green), and when higher than a value (alert in orange)...all within one cell. Any help how to do so?

 

[etaf]

you can use conditional formatting
but it would be useful to know the layout of the data

what cell is the "value" you are comparing in

lets assume it was in A1
and all the other values are in
A2 to Z2

the you would setup three rules

for 2007 or 2010 excel version
Conditional Formatting


Highlight applicable range >>
A2:Z2


Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:


= A2<$A$1


Format… [Number, Font, Border, Fill] - Fill RED
choose the format you would like to apply when the condition is true
OK >> OK

Next formula

=AND( A2>= $A$1 , A2<= $A$1 )

fill for Green

=A2 > $A$1
fill for yellow

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	10
2	1.5	2	2.5	3	3.5	4	4.5	5	5.5	6	6.5	7	7.5	8	8.5	9	9.5	10	10.5	11	11.5	12	12.5	13	13.5	14
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2	1. / Formula is =AND(A2<=$A$1, A2>=$A$1)	Abc
A2	2. / Formula is =A2>$A$1	Abc
A2	3. / Formula is =A2<$A$1	Abc

 

[loiacono9]

My data range is D6 (736) and E6 (1166) and want to have the changing value in H6 alerted low in Red, within range in green, and above range in orange. Does that make sense?

 

[etaf]

try this

Excel Workbook
	D	E	F	G	H
6	736	1166			735
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H6	1. / Formula is =AND(H6>=D6,H6<=E6)	Abc
H6	2. / Formula is =H6>E6	Abc
H6	3. / Formula is =H6	Abc

 

[R Pelle]

Another solution is perhaps to show traffic lights (and value 735) in H6. BUT it can ONLY compare to 1 range as in fixed $D$6 and fixed $E$6
Traffic lights red amber green. green trf light when value is >=, Value, =$E$6 ,type, Formula. Amber when < formula and, >= , =$D$6, Formula
You can set the range to e.g. H6:H100 but it will ONLY compare to D6,E6.