Conditional formatting problem - MS' definition of "between"

  • Thread starter Thread starter Elardus
  • Start date Start date
E

Elardus

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 .....?
 
--Select the range of cells. Assume this range is A1:A7

--Goto Home tab>Styles>Conditional Formatting>Manage rules>New rule>Use a
formula to determine which cells to format

--Enter this formula in the box below:
=AND(A1>0A1<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
 
Try this...

For shading the cells which is having the values between 0 to 3 Try this.
=IF(A1="","",AND(A1>=0,A1<=3))

Place the cursor in A1 and select the Range (Active Cell Should Be A1)

1) Go to Conditional formatting and select “New Rule†and select “Use a
Formula to determine which cells to formatâ€
2) Paste the formula in “Format Values Where this formula is true†field
3) Click Format Button> and set your desired Font & Background Color and
give ok.

You can change the Formula refence A1 cell to your desired cell, but note
that while creating the conditional formatting the cursor should be in the
active cell as per the formula.

(Ex. here A1 is the Active cell so you have to place the cursor in A1 and
after that you have to select the Entire A column)

If this post helps, Click Yes!
 
I missed a comma inbetween;try
=AND(A1>0,A1<3)

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

If this post helps click Yes
 
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(A1>0,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(A1>A9,A1<A10) (having entered 0 in
A9 and 3 in A10). Except this does not work...
 
Just add a $ symbol before the column no and Cell No to freeze the A9 and A10
Cells.

=AND(A1>$A$9,A1<$A$10)
 
--If your selection is cell A1 alone then the below formula will work fine.
=AND(A1>A9,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(A2>A10,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
 
Hi Jacob

I've just found the solution: my "A9" and "A10" should have been "$A$9" and
"$A$10".
 
Thanks. This worked as well. I'll evidently have to get used to using
formuale instead of the easier (but sometimes unreliable) "Format only cells
that contain" option.
 
Back
Top