Format 2 colors

O

Old Red One

Using Formula =IF(T1399>2000000,"Heavy","Normal"), with conditional
formatting I can color Heavy when it pertains, but I cannot color Normal
when it applies. How can I have two colors depending on the True or False
of the formula?
 
M

Max

Add a second condition, eg

Condition 1:
=AND(ISNUMBER(E1),E1>20)
Format as desired

Condition 2:
=AND(E1<=20,E1<>"")
Format as desired
 
O

Old Red One

I am trying to format cell color if cell value is greater than a particular
number, and format the same cell in a different color if the cell value is
less than the particular number. I can use the ISNUMBER function suggested
but it seems to require two different entries; i.g. ISNUMBER> in one cell,
ISNUMBER< in another cell. Can my objective be met using Conditional
Formatting from the Format menu? The particular number is the daily total
NYSE Volume. Please comment and advise. Thank you.
 
M

Max

As per the earlier suggestion, if you want the cell to be formatted in a
different color for different values, use another/2nd CF condition.

If the earlier suggestion didn't work for you, then the problem could be
that the source underlying numbers in the col (your NYSE vol numbers) are
text numbers, not real numbers. In which case, we could try coercing the text
numbers to real numbers via adding a zero: "+0", ie try instead:

Condition 1:
=AND(ISNUMBER(E1+0),E1+0>20)
Format as desired

Condition 2:
=AND(ISNUMBER(E1+0),E1+0<=20,E1<>"")
Format as desired

Adapt the above to suit the column to be formatted
(I used col E)
 
O

Old Red One

Both of your suggested Conditions work (i.e. both with and w/o the '+0'
feature. Prehaps I should add: I use the number twenty (20) only as an
attempt at brevity - when making entries on the actual worksheet the number
will be the greater or less than 2 Billion. Further, if I understand your
suggestions, I need to enter condition 1 in a separate cell, e.g. F1; and
then enter Condition 2 in another separate cell, e.g. F3. (Is that correct?)
I had hoped to use a formula (yours, or "IF") entered in one cell only, and
then format using the Conditional Format option on the drop down Format
menu. I was able to PARTIALLY achieve this, getting a color into the -
say - Greater than cell, but was unable to get a different color into the
less than cell when using 'add a second condition.'
I was also trying to use NORMAL and HEAVY in place of TRUE & FALSE.
Perhaps I ask too much?
 
M

Max

.. Further, if I understand your suggestions, I need to enter condition 1
in a separate cell, e.g. F1; and then enter Condition 2 in another
separate cell, e.g. F3. (Is that correct?)
Click to expand...

The CF is meant to be applied to the entire col at one go, eg:

Select the entire col E (select the col header)

Click Format > Conditional Formatting

Condition 1, Formula is:
=AND(ISNUMBER(E1+0),E1+0>20)
Format as desired

Click Add (to add condition 2)

Condition 2, Formula is:
=AND(ISNUMBER(E1+0),E1+0<=20,E1<>"")
Format as desired

Click OK
 
M

Max

I was also trying to use NORMAL and HEAVY in place of TRUE & FALSE.

Assuming you already have an IF formula in col E returning NORMAL and HEAVY,
you could try this for the CF

Select the entire col E (select the col header)

Click Format > Conditional Formatting

Condition 1, Formula is:
=E1="Heavy"
Format as desired

Click Add (to add condition 2)

Condition 2, Formula is:
=E1="Normal"
Format as desired

Click OK
 
Top