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 ...

		#1	#2	#3	#4	#5	#6	#7
#1			322	163	291	89	80	416
#2		253		22	39	42	43	312
#3		70	9		7	9	7	68
#4		164	31	14		27	26	173
#5		63	35	20	32		25	130
#6		50	30	12	22	20		86

<colgroup><col><col><col span="7"></colgroup><tbody>
</tbody>

 

[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
3		#1	#2	#3	#4	#5	#6	#7
4	#1		322	163	291	89	80	416
5	#2	253		22	39	42	43	312
6	#3	70	9		7	9	7	68
7	#4	164	31	14		27	26	173
8	#5	63	35	20	32		25	130
9	#6	50	30	12	22	20		86

<tbody>
</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