| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula with color fill?
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula with color fill?
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$10 Mike "Positive" wrote: 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula with color fill?
Try this:
Select the range of cells A1:D1 Goto FormatConditional 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),$A10) Click the Format button Select the Patterns tab Select GREEN OK out -- Biff Microsoft Excel MVP "Positive" wrote in message ps.com... 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula with color fill?
On Jul 13, 2:20 pm, "T. Valko" wrote:
Try this: Select the range of cells A1:D1 Goto FormatConditional 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),$A10) Click the Format button Select the Patterns tab Select GREEN OK out -- Biff Microsoft Excel MVP "Positive" wrote in message ps.com... 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- Hide quoted text - - Show quoted text - 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 = $A10 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula with color fill?
On Jul 13, 3:01 pm, Positive wrote:
On Jul 13, 2:20 pm, "T. Valko" wrote: Try this: Select the range of cells A1:D1 Goto FormatConditional 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),$A10) Click the Format button Select the Patterns tab Select GREEN OK out -- Biff Microsoft Excel MVP "Positive" wrote in message ups.com... 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- Hide quoted text - - Show quoted text - 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 = $A10 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 - 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula with color fill?
try and put
another one maybe like this by first making sure that the "value" is a number formula is: =ISNUMBER($A1)*($A10) 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. Valko" wrote: Try this: Select the range of cells A1:D1 Goto FormatConditional 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),$A10) Click the Format button Select the Patterns tab Select GREEN OK out -- Biff Microsoft Excel MVP "Positive" wrote in message ps.com... 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula with color fill?
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 = $A10 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 - 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 Ah, OK. Well, we both assumed that (+) meant positive NUMBERS and (-) meant NEGATIVE numbers. Try this: Select the range of cells A1:D1 Goto FormatConditional 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$10 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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula with color fill?
On Jul 13, 4:11 pm, "T. Valko" wrote:
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 = $A10 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 - 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 Ah, OK. Well, we both assumed that (+) meant positive NUMBERS and (-) meant NEGATIVE numbers. Try this: Select the range of cells A1:D1 Goto FormatConditional 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$10 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 - THANK YOU- IT WORKS GREAT :-) Btw, how do I apply this formula for hundreds of cells without having to repeat? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula with color fill?
"Positive" wrote in message
ups.com... On Jul 13, 4:11 pm, "T. Valko" wrote: 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 = $A10 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 - 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 Ah, OK. Well, we both assumed that (+) meant positive NUMBERS and (-) meant NEGATIVE numbers. Try this: Select the range of cells A1:D1 Goto FormatConditional 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$10 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 - THANK YOU- IT WORKS GREAT :-) Btw, how do I apply this formula for hundreds of cells without having to repeat? 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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula with color fill?
On Jul 13, 4:50 pm, "T. Valko" wrote:
"Positive" wrote in message ups.com... On Jul 13, 4:11 pm, "T. Valko" wrote: 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 = $A10 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 - 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 Ah, OK. Well, we both assumed that (+) meant positive NUMBERS and (-) meant NEGATIVE numbers. Try this: Select the range of cells A1:D1 Goto FormatConditional 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$10 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 - THANK YOU- IT WORKS GREAT :-) Btw, how do I apply this formula for hundreds of cells without having to repeat? 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 - 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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula with color fill?
"Positive" wrote in message
ps.com... On Jul 13, 4:50 pm, "T. Valko" wrote: "Positive" wrote in message ups.com... On Jul 13, 4:11 pm, "T. Valko" wrote: 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 = $A10 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 - 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 Ah, OK. Well, we both assumed that (+) meant positive NUMBERS and (-) meant NEGATIVE numbers. Try this: Select the range of cells A1:D1 Goto FormatConditional 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$10 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 - THANK YOU- IT WORKS GREAT :-) Btw, how do I apply this formula for hundreds of cells without having to repeat? 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 - 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 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 FormatConditional 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 |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula with color fill?
On Jul 16, 1:20 pm, "T. Valko" wrote:
"Positive" wrote in message ps.com... On Jul 13, 4:50 pm, "T. Valko" wrote: "Positive" wrote in message roups.com... On Jul 13, 4:11 pm, "T. Valko" wrote: 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 = $A10 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 - 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 Ah, OK. Well, we both assumed that (+) meant positive NUMBERS and (-) meant NEGATIVE numbers. Try this: Select the range of cells A1:D1 Goto FormatConditional 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$10 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 - THANK YOU- IT WORKS GREAT :-) Btw, how do I apply this formula for hundreds of cells without having to repeat? 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 - 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 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 FormatConditional 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 - Thanks Biff- You are the man! |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula with color fill?
"Positive" wrote in message
ups.com... On Jul 16, 1:20 pm, "T. Valko" wrote: "Positive" wrote in message ps.com... On Jul 13, 4:50 pm, "T. Valko" wrote: "Positive" wrote in message roups.com... On Jul 13, 4:11 pm, "T. Valko" wrote: 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 = $A10 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 - 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 Ah, OK. Well, we both assumed that (+) meant positive NUMBERS and (-) meant NEGATIVE numbers. Try this: Select the range of cells A1:D1 Goto FormatConditional 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$10 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 - THANK YOU- IT WORKS GREAT :-) Btw, how do I apply this formula for hundreds of cells without having to repeat? 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 - 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 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 FormatConditional 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 - Thanks Biff- You are the man! You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do you Control the fill color using an if statement formula? | Excel Worksheet Functions | |||
| Formula for a fill color help | New Users to Excel | |||
| allow fill color to be changed or toggled using a formula | Excel Discussion (Misc queries) | |||
| can I use the 'fill color' as part of a formula | Excel Discussion (Misc queries) | |||
| Formula to count the cells in a range that have a fill color. | Excel Discussion (Misc queries) | |||
Linear Mode
