Help using Less Than, Greater Than in same formula statement!

[Ultimate Selector]

What would the formula be for the following, in using conditional formatting (to determine cell to format).

I can't seem to use both in the same rule. I even tried doing them separately in their own rule but no go.

Here is my statement ... Greater than 1 (>1) but Less than 10 (<10).

Exactly how do I write this formula in the same statement to be able to format this cell I'm trying to Highlight from a different range of numbers that follow this rule?

I know it's simple but I'm brain dead trying to get this done!

Thanks for any help on this!

Regards,
U.S.

 

[jtakw]

Hi,

Not following regarding

format this cell I'm trying to Highlight from a different range of numbers that follow this rule

But based on the rest of the description, CF, use formula:

Book1
	A	B
1	1	FALSE
2	2	TRUE
3	3	TRUE
4	4	TRUE
5	5	TRUE
6	6	TRUE
7	7	TRUE
8	8	TRUE
9	9	TRUE
10	10	FALSE
11	11	FALSE
12	12	FALSE
13	13	FALSE
14	14	FALSE
Sheet420

Cell Formulas
Range	Formula
B1	=AND(A1>1,A1<10)

 

[Ultimate Selector]

Thanks Jtakw! I haven't got it to work just yet with your advice. I know I'm doing something wrong.

I'm trying to highlight #3 cuz there are four numbers that are less than 10 but more than 1.
I tried this but it's not quite right ... =AND(D4:Q4>1,D4:Q4<10) ... this is the range I'm working with.

Any more suggestions or ideas?

Thanks again!

Here is my layout ...

[TABLE="width: 519"]
<colgroup><col><col><col span="7"></colgroup><tbody>[TR]
[TD] [/TD]
[TD] [/TD]
[TD]#1[/TD]
[TD]#2[/TD]
[TD]#3 [/TD]
[TD]#4[/TD]
[TD]#5[/TD]
[TD]#6[/TD]
[TD]#7[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]#1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]322[/TD]
[TD]163[/TD]
[TD]291[/TD]
[TD]89[/TD]
[TD]80[/TD]
[TD]416[/TD]
[/TR]
[TR]
[TD]#2[/TD]
[TD] [/TD]
[TD]253[/TD]
[TD] [/TD]
[TD]22[/TD]
[TD]39[/TD]
[TD]42[/TD]
[TD]43[/TD]
[TD]312[/TD]
[/TR]
[TR]
[TD]#3 [/TD]
[TD] [/TD]
[TD]70[/TD]
[TD]9
[/TD]
[TD][/TD]
[TD]7[/TD]
[TD]9[/TD]
[TD]7[/TD]
[TD]68[/TD]
[/TR]
[TR]
[TD]#4[/TD]
[TD] [/TD]
[TD]164[/TD]
[TD]31[/TD]
[TD]14[/TD]
[TD] [/TD]
[TD]27[/TD]
[TD]26[/TD]
[TD]173[/TD]
[/TR]
[TR]
[TD]#5[/TD]
[TD] [/TD]
[TD]63[/TD]
[TD]35[/TD]
[TD]20
[/TD]
[TD]32[/TD]
[TD] [/TD]
[TD]25[/TD]
[TD]130[/TD]
[/TR]
[TR]
[TD]#6[/TD]
[TD] [/TD]
[TD]50[/TD]
[TD]30[/TD]
[TD]12[/TD]
[TD]22[/TD]
[TD]20[/TD]
[TD] [/TD]
[TD]86[/TD]
[/TR]
</tbody>[/TABLE]

 

[MARK858]

You should be only using the cell reference of the first cell in the range i.e.
=AND(D4>1,D4<10)

 

[Ultimate Selector]

You should be only using the cell reference of the first cell in the range i.e.
=AND(D4>1,D4<10)

I have tried that and nothing happens. I'm stumped here ...

Thanks tho Mark, any other suggestions?

 

[Ultimate Selector]

Just a bump here as I still have not found a solution to this statement using greater than and less than in the same line of formula for conditional formatting.

All Help is Appreciated.

Thanks,
U.S.

 

[MARK858]

Now I am home looking at this on a PC I am wondering what is the exact condition that Makes #3 get highlighted?
If it is just if any cell is there between those criteria then...

<b>Sheet2</b><br /><br /><table border="0" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td >#1</td><td >#2</td><td style="background-color:#ff0000; ">#3</td><td >#4</td><td >#5</td><td >#6</td><td >#7</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >#1</td><td > </td><td style="text-align:right; ">322</td><td style="text-align:right; ">163</td><td style="text-align:right; ">291</td><td style="text-align:right; ">89</td><td style="text-align:right; ">80</td><td style="text-align:right; ">416</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >#2</td><td style="text-align:right; ">253</td><td > </td><td style="text-align:right; ">22</td><td style="text-align:right; ">39</td><td style="text-align:right; ">42</td><td style="text-align:right; ">43</td><td style="text-align:right; ">312</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >#3</td><td style="text-align:right; ">70</td><td style="text-align:right; ">9</td><td > </td><td style="text-align:right; ">7</td><td style="text-align:right; ">9</td><td style="text-align:right; ">7</td><td style="text-align:right; ">68</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >#4</td><td style="text-align:right; ">164</td><td style="text-align:right; ">31</td><td style="text-align:right; ">14</td><td > </td><td style="text-align:right; ">27</td><td style="text-align:right; ">26</td><td style="text-align:right; ">173</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >#5</td><td style="text-align:right; ">63</td><td style="text-align:right; ">35</td><td style="text-align:right; ">20</td><td style="text-align:right; ">32</td><td > </td><td style="text-align:right; ">25</td><td style="text-align:right; ">130</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >#6</td><td style="text-align:right; ">50</td><td style="text-align:right; ">30</td><td style="text-align:right; ">12</td><td style="text-align:right; ">22</td><td style="text-align:right; ">20</td><td > </td><td style="text-align:right; ">86</td></tr></table> <br /><br /><span </a>

=COUNTIFS($D$4:$J$9,">1",$D$4:$J$9,"<10")>0

 

[Ultimate Selector]

Now I am home looking at this on a PC I am wondering what is the exact condition that Makes #3 get highlighted?
If it is just if any cell is there between those criteria then...

Sheet2

	C	D	E	F	G	H	I	J
	#1	#2	#4	#5	#6	#7
#1
#2
#3
#4
#5
#6

<tbody>
[TD="align: center"]3[/TD]

[TD="bgcolor: #ff0000"]#3[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]322[/TD]
[TD="align: right"]163[/TD]
[TD="align: right"]291[/TD]
[TD="align: right"]89[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]416[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]253[/TD]

[TD="align: right"]22[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]312[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]70[/TD]
[TD="align: right"]9[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]68[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]164[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]14[/TD]

[TD="align: right"]27[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]173[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]63[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]32[/TD]

[TD="align: right"]25[/TD]
[TD="align: right"]130[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]50[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]20[/TD]

[TD="align: right"]86[/TD]

</tbody>

The rows are read from left to right only. The #3 would get Highlighted only because it's range from left to right posses the numbers (9,7,9,7) that are less than 10 but greater than 1 in that row.

Now if row #6 had a number say 5 then it too would be highlighted.

However, I'm having an issue cuz I have to have in the formula stated that It's >1 but <10.
Thanks Mark!

 

[MARK858]

T

However, I'm having an issue cuz I have to have in the formula stated that It's >1 but <10.
Thanks Mark!

Does

=COUNTIFS($D$4:$J$9,">1",$D$4:$J$9,"<10")>0

not have >1 but <10 stated in the formula

 

[MARK858]

<b>Sheet2</b><br /><br /><table border="0" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td >#1</td><td >#2</td><td >#3</td><td >#4</td><td >#5</td><td >#6</td><td >#7</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="background-color:#92d050; ">#1</td><td style="text-align:right; ">4</td><td style="text-align:right; ">322</td><td style="text-align:right; ">163</td><td style="text-align:right; ">291</td><td style="text-align:right; ">89</td><td style="text-align:right; ">80</td><td style="text-align:right; ">416</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >#2</td><td style="text-align:right; ">253</td><td > </td><td style="text-align:right; ">22</td><td style="text-align:right; ">39</td><td style="text-align:right; ">42</td><td style="text-align:right; ">43</td><td style="text-align:right; ">312</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="background-color:#92d050; ">#3</td><td style="text-align:right; ">70</td><td style="text-align:right; ">9</td><td > </td><td style="text-align:right; ">7</td><td style="text-align:right; ">9</td><td style="text-align:right; ">7</td><td style="text-align:right; ">68</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >#4</td><td style="text-align:right; ">164</td><td style="text-align:right; ">31</td><td style="text-align:right; ">14</td><td > </td><td style="text-align:right; ">27</td><td style="text-align:right; ">26</td><td style="text-align:right; ">173</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >#5</td><td style="text-align:right; ">63</td><td style="text-align:right; ">35</td><td style="text-align:right; ">20</td><td style="text-align:right; ">32</td><td > </td><td style="text-align:right; ">25</td><td style="text-align:right; ">130</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="background-color:#92d050; ">#6</td><td style="text-align:right; ">50</td><td style="text-align:right; ">30</td><td style="text-align:right; ">12</td><td style="text-align:right; ">3</td><td style="text-align:right; ">20</td><td > </td><td style="text-align:right; ">86</td></tr></table> <br /><br /><span

=COUNTIFS($D4:$J4,">1",$D4:$J4,"<10")>0