Formula with color fill?

  • Thread starter Thread starter Positive
  • Start date Start date
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
 
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
 
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
 
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
 
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
 
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
 
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.
 
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?
 
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 "-" ?
 
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
 
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.
 
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!
 
Back
Top