On 1/26/2017 2:02 AM, Brian Barker wrote:
At 21:46 25/01/2017 -0500, Vince Bonly wrote:
Using AOO 4.1.2 Calc on my WIN10 desktop, I have succeeded in
detecting scores above a threshold (>174) and marking the cell for
red font for a specified range of cells. That uses 1 of the 3
available criteria settings. Next, I tried setting a criteria setting
(condition 2 of 3) for the same range of cells to apply a bold font
whenever scores are >199. Thus, for example, a score of 225 would
have both red and bold applied to the cell. It seems that Calc is
unable to set a 2nd or 3rd criteria over *the same range of cells*.
By experiment, I see that is not true: you can achieve what you want.
Unfortunately, you don't explain exactly what you have done and what
result you see, so anyone is left to guess.
OK; I can verify FYI that at the time of my OP I had the following
settings at the Format*|*Conditional Formatting dialogue:
Condition 1:
Cell value is greater than 74
Cell Style Red if >174
Condition 2:
Cell value is greater than 199
Cell Style BOLD if >199 Scored
Condition 3:
Cell value is greater than 199
Cell Style 11 pt if >199 Scored
Conditional formatting works by applying cell styles. You talk about
applying "both red and bold", but you cannot apply two styles
simultaneously to the same cell or cell range, of course. So you would
need one cell style for red and another for red-bold. (The criteria
you wish to apply are such that you don't need bold without red.) Is
that what you did?
No, I did not. (See above.)
All that's left is to notice the built-in help text, which says _inter
alia_ "The conditions are evaluated from 1 to 3. If the condition 1
matches the condition, the defined style will be used. Otherwise,
condition 2 is evaluated, and its defined style used. If this style
does not match, condition 3 is evaluated." (This doesn't appear to be
explained in the Calc Guide.) Note those words "Otherwise" and "If ...
not": if any condition is satisfied, later ones are disregarded. In
your case, if you apply the >174 condition first, the >199 condition
will be evaluated only if the value is not >174 - so can never be
effective. The solution is just to set >199 with red-bold as Condition
1 and >174 with red as Condition 2.
Thank you, Brian. Indeed, very helpful, and key to grasping what is
going on when using Format*|*Conditional Formatting feature in Calc.
Is a Boolean AND required/possible in the criteria settings?
Not if you are using "Cell value is", I think.
Is the 3-criteria a limit for each Calc sheet?
No: it's a limit for any individual cell, I think. However you
construct your cell ranges, each cell has its own set of up to three
conditions.
I trust this helps.
Brian Barker
_____________________________________________________________________
FYI, I now have the following settings; they work satisfactorily for my
needs:
Condition 1 cell style:
Cell value is greater than 199.
Cell Style*|*Font tab: I have selected Arial-Font; Bold-Typeface; and 11
pt-Size as the Font cell style definitions.
Cell Style*|*Font Effects tab: I have selected Red-character color as
the Font Effects cell style definition.
Condition 2 cell style:
Cell value is greater than 174.
Cell Style*|*Font Effects tab: I have selected Red as the Font Effects
cell style definition.
Condition 3 cell style:
Cell value is greater between 1 and 100.
Cell Style*|*Background tab: I have selected YELLOW fill-in color as the
Background cell style definition.
I hope that my info is also helpful.
My TNX for your detailed discussion.
Regards,
VinceB.
