Color formatting with two conditions

[Memar]

I want excel automatically to color cells which are below 80% and above 110%. Please note there is a formula written to get the number of the percentages =SUMIF($D$5:$D$30,$D$37,L5:L30). Thus, the formula to formatting the color should be included in the example formula above.


[TABLE="width: 372"]
<colgroup><col style="width:47pt" span="6" width="62"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 62, align: right"]105%[/TD]
[TD="class: xl63, width: 62, align: right"]110%[/TD]
[TD="class: xl63, width: 62, align: right"]90%[/TD]
[TD="class: xl63, width: 62, align: right"]86%[/TD]
[TD="class: xl63, width: 62, align: right"]70%[/TD]
[TD="class: xl63, width: 62, align: right"]40%[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]93%[/TD]
[TD="class: xl63, align: right"]100%[/TD]
[TD="class: xl63, align: right"]100%[/TD]
[TD="class: xl63, align: right"]130%[/TD]
[TD="class: xl63, align: right"]80%[/TD]
[TD="class: xl63, align: right"]60%[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]82%[/TD]
[TD="class: xl63, align: right"]82%[/TD]
[TD="class: xl63, align: right"]90%[/TD]
[TD="class: xl63, align: right"]65%[/TD]
[TD="class: xl63, align: right"]57%[/TD]
[TD="class: xl63, align: right"]17%[/TD]
[/TR]
</tbody>[/TABLE]


desired result

[TABLE="width: 372"]
<colgroup><col style="width:47pt" span="6" width="62"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 62, align: right"][TABLE="width: 376"]
<tbody>[TR]
[TD]<!--[if gte mso 9]><xml> <o:OfficeDocumentSettings> <o:AllowPNG/> </o:OfficeDocumentSettings> </xml><![endif]--> [TABLE="class: MsoNormalTable, width: 282"]
<tbody>[TR]
[TD="width: 63"]

105%​

[/TD]
[TD="width: 63"]

110%​

[/TD]
[TD="width: 63"]

90%​

[/TD]
[TD="width: 63"]

86%​

[/TD]
[TD="width: 63"]

70%​

[/TD]
[TD="width: 63"]

40%​

[/TD]
[/TR]
[TR]
[TD="width: 63"]

93%​

[/TD]
[TD="width: 63"]

100%​

[/TD]
[TD="width: 63"]

100%​

[/TD]
[TD="width: 63"]

130%​

[/TD]
[TD="width: 63"]

80%​

[/TD]
[TD="width: 63"]

60%​

[/TD]
[/TR]
[TR]
[TD="width: 63"]

82%​

[/TD]
[TD="width: 63"]

82%​

[/TD]
[TD="width: 63"]

90%​

[/TD]
[TD="width: 63"]

65%​

[/TD]
[TD="width: 63"]

57%​

[/TD]
[TD="width: 63"]

17%​

[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="class: xl65, width: 62, align: right"][/TD]
[TD="class: xl66, width: 62, align: right"][/TD]
[TD="class: xl66, width: 62, align: right"][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"][/TD]
[TD="class: xl65, align: right"][/TD]
[TD="class: xl65, align: right"][/TD]
[TD="class: xl66, align: right"][/TD]
[TD="class: xl65, align: right"][/TD]
[TD="class: xl66, align: right"][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"][/TD]
[TD="class: xl65, align: right"][/TD]
[TD="class: xl65, align: right"][/TD]
[TD="class: xl66, align: right"]
[/TD]
[TD="class: xl66, align: right"]
[/TD]
[TD="class: xl66, align: right"]
[/TD]
[/TR]
</tbody>[/TABLE]


Thanks for your help and time.

 

[NeonRedSharpie]

Just highlight your cells and go to conditional formatting. Then put in two rules using the "above" and "below" buttons. One is above 1.1, and then other is below .8.


eta: I guess they're actually "greater than" and "less than"

 

[Special-K99]

Select top left hand cell, e.g. A1

Conditional Formatting
New Rule
Use a formula to determine...

=(A1 < 80%)
format as required

=(A1 > 110%)
format as required

Use Format painter (paintbrush icon) to copy to other cells