Hi Jacob

I've just found the solution: my "A9" and "A10" should have been "$A$9" and
"$A$10".

"Jacob Skaria" wrote:

--If your selection is cell A1 alone then the below formula will work fine.
=AND(A1A9,A1<A10)

--If your selection is A1:A10 and if you use the above formula the
conditional formatting formula for cell A2 will change as =AND(A2A10,A2<A11)
which is not what you needed. So you need to lock the cells A9 and A10 using
absolute referencing. So with multiple cell selection use the below formula
=AND(A1$A$9,A1<$A$10)


PS: A note on different reference styles (incase if you are unaware)

A1 Relative referencing. Both column and row will change if you copy or drag
the formula.
$A1 The column reference is fixed and will not change
A$1 The row reference is fixed and will not change.
$A$1 Column and row reference are fixed.

If this post helps click Yes
---------------
Jacob Skaria


"Elardus" wrote:

Hi Jacob

Thanks, that helped somewhat, but I'm afraid I'm still not quite satisfied.

I admit I haven't much explored the option "Use a formula to determine which
cells to format" as I have rather been using the option "Format only cells
that contain", which seems the same as it used to be in Excel 2003.

Your example =AND(A10,A1<3) did work perfect though, but as I prefer to use
cell references in formulae (instead of the explicit values 0 and 3 above), I
then amended the formula as follows: =AND(A1A9,A1<A10) (having entered 0 in
A9 and 3 in A10). Except this does not work...


"Jacob Skaria" wrote:

I missed a comma inbetween;try
=AND(A10,A1<3)

If you want to include 0 and 3; then
=AND(A1<"",A1=0,A1<=3)

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

--Select the range of cells. Assume this range is A1:A7

--Goto Home tabStylesConditional FormattingManage rulesNew ruleUse a
formula to determine which cells to format

--Enter this formula in the box below:
=AND(A10A1<3)
Please note that the cell reference A1 is the active cell in the selection.
Active cell willhave the white background even after selection

--Click the Format button. Select the desired style(s) and click OK
If this post helps click Yes
---------------
Jacob Skaria


"Elardus" wrote:

In my mind , the notion "between" should mean that the limits used to define
"between" should not be part of the resulting answer. Seemingly not so with
Excel 2007 conditional formatting though - When I add a rule to highlight
values between say 0 and 3, I was hoping not to see the values 0 and 3
highlighted....

How do I solve this without reverting to unsatisfactory "between" limits of
something like 0.00001 and 2.99999 .....?