Formula with color fill?

P

Positive

Is there any way that we can create a formula when , eg, A1 is certain
value (-), then the whole range A1: D1 is filled with yellow color ;
A1 is certain value (+), then the whole range A1:D1 is filled with
green color?

Thanks
Lan
 
M

Mike H

Yes there is, Select A1 - D1 and then

Format|conditional formatting|select formula is from the dropdown
Paste this in and select a colour
=$A$1<=0
Click ADD and repeat using the formula
=$A$1>0

Mike
 
T

T. Valko

Try this:

Select the range of cells A1:D1
Goto Format>Conditional Formatting
Condition 1
Formula Is: =$A1<0
Click the Format button
Select the Patterns tab
Select YELLOW
OK
Click the Add button
Condition 2
Formula Is: =AND(ISNUMBER($A1),$A1>0)
Click the Format button
Select the Patterns tab
Select GREEN
OK out
 
P

Positive

Try this:

Select the range of cells A1:D1
Goto Format>Conditional Formatting
Condition 1
Formula Is: =$A1<0
Click the Format button
Select the Patterns tab
Select YELLOW
OK
Click the Add button
Condition 2
Formula Is: =AND(ISNUMBER($A1),$A1>0)
Click the Format button
Select the Patterns tab
Select GREEN
OK out

--
Biff
Microsoft Excel MVP







- Show quoted text -
Click to expand...

Thank you so much guys. It works. But I have a question. Why does it
have to be =AND(ISNUMBER when it can simply be = $A1>0

Another question is, what if I have hundreds of cells needed to be
conditional formatting like that, what shoudl I do without repeating?

AGAIN! THANKS A BUNCH
 
P

Positive

Thank you so much guys. It works. But I have a question. Why does it
have to be =AND(ISNUMBER when it can simply be = $A1>0

Another question is, what if I have hundreds of cells needed to be
conditional formatting like that, what shoudl I do without repeating?

AGAIN! THANKS A BUNCH- Hide quoted text -

- Show quoted text -
Click to expand...

Hang on, it works but only the GREEN color apprears regardless of (+)
or (-) when I use Mike's formula. When I use Biff's then no color
shows. Maybe i did not explain myself very clear.

In my A1 cell, I don't have any numeric value. All I have is plus
sign( +), minus sign(-) and (0). So GREEN fill should be for (+),
YELLOW for (-) and no fill for (0). Please help- THANKS
 
D

driller

try and put
another one maybe like this
by first making sure that the "value" is a number

formula is:

=ISNUMBER($A1)*($A1>0)

you can extend also more-more criteria by"*".


regards,
driller
--
*****
birds of the same feather flock together..birdwise, it''s more worth
knowing the edges rather than focusing in one line! Know the limits and
remember the extents - dive with Jonathan Seagull
 
T

T. Valko

Thank you so much guys. It works. But I have a question. Why does it
Hang on, it works but only the GREEN color apprears regardless of (+)
or (-) when I use Mike's formula. When I use Biff's then no color
shows. Maybe i did not explain myself very clear.

In my A1 cell, I don't have any numeric value. All I have is plus
sign( +), minus sign(-) and (0). So GREEN fill should be for (+),
YELLOW for (-) and no fill for (0). Please help- THANKS
Click to expand...

Ah, OK. Well, we both assumed that (+) meant positive NUMBERS and (-) meant
NEGATIVE numbers.

Try this:

Select the range of cells A1:D1
Goto Format>Conditional Formatting
Condition 1
Formula Is: =$A1="-"
Click the Format button
Select the Patterns tab
Select YELLOW
OK
Click the Add button
Condition 2
Formula Is: =$A1="+"
Click the Format button
Select the Patterns tab
Select GREEN
OK out

The resaon Mike's worked for GREEN was when you entered either "+" or "-",
those are TEXT values and any TEXT value evaluates to be greater than any
number. So the conditional test: =$A$1>0 would evaluate to TRUE and apply
the GREEN color. My formulas didn't work at all because mine were
specifically expecting NUMBERS to be entered.
 
P

Positive

Ah, OK. Well, we both assumed that (+) meant positive NUMBERS and (-) meant
NEGATIVE numbers.

Try this:

Select the range of cells A1:D1
Goto Format>Conditional Formatting
Condition 1
Formula Is: =$A1="-"
Click the Format button
Select the Patterns tab
Select YELLOW
OK
Click the Add button
Condition 2
Formula Is: =$A1="+"
Click the Format button
Select the Patterns tab
Select GREEN
OK out

The resaon Mike's worked for GREEN was when you entered either "+" or "-",
those are TEXT values and any TEXT value evaluates to be greater than any
number. So the conditional test: =$A$1>0 would evaluate to TRUE and apply
the GREEN color. My formulas didn't work at all because mine were
specifically expecting NUMBERS to be entered.

--
Biff
Microsoft Excel MVP- Hide quoted text -

- Show quoted text -
Click to expand...

THANK YOU- IT WORKS GREAT :)
Btw, how do I apply this formula for hundreds of cells without having
to repeat?
 
T

T. Valko

Positive said:
THANK YOU- IT WORKS GREAT :)
Btw, how do I apply this formula for hundreds of cells without having
to repeat?
Click to expand...

Well, that depends. Are all the cells in a contiguous block? Are all the
cells based on cell A1 being either "+" or "-" ?
 
P

Positive

Well, that depends. Are all the cells in a contiguous block? Are all the
cells based on cell A1 being either "+" or "-" ?

--
Biff
Microsoft Excel MVP- Hide quoted text -

- Show quoted text -
Click to expand...

Biff,
All the cells are contiguous. Range A1:D1 depends on cell A1 to turn
GREEN or YELLOW. Range A2:D2 depends on cell A2, A3:D3 depends on cell
A3, A100:D100 depends on A100 ...

Many thanks
 
T

T. Valko

Positive said:
Biff,
All the cells are contiguous. Range A1:D1 depends on cell A1 to turn
GREEN or YELLOW. Range A2:D2 depends on cell A2, A3:D3 depends on cell
A3, A100:D100 depends on A100 ...

Many thanks
Click to expand...

OK, you can do this all in a single operation.

Suppose your range is A1:D100

Hit function key F5
In the Reference box type A1:D100
Click OK

That will select the range of cells A1:D100 with A1 being the active cell.
Then goto Format>Conditional Formatting and follow the steps outlined
earlier.
Enter the formulas *exactly* as shown in the earlier reply.

The cell references will automatically adjust for the proper cells.
 
P

Positive

OK, you can do this all in a single operation.

Suppose your range is A1:D100

Hit function key F5
In the Reference box type A1:D100
Click OK

That will select the range of cells A1:D100 with A1 being the active cell.
Then goto Format>Conditional Formatting and follow the steps outlined
earlier.
Enter the formulas *exactly* as shown in the earlier reply.

The cell references will automatically adjust for the proper cells.

--
Biff
Microsoft Excel MVP- Hide quoted text -

- Show quoted text -
Click to expand...

Thanks Biff- You are the man!
 
Top