Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
Good afternoon,
I have come up against a problem regarding conditional formatting. I have this formula in cell s22 ... '=IF(OR(G22<1,G2256)," ",IF(ISERROR(HLOOKUP(G22,$B$7:$AC $8,2,0)),HLOOKUP(G22,$B$9:$AC$10,2,0),HLOOKUP(G22, $B$7:$AC$8,2,0))) .... with the cells formated as ... Font = White No Border No Pattern I have ... Condition 1 = Cell Value Is greater than 0 Font = Black Border = Outline & Pattern Colour = Grey Condition 2 = Cell Value Is equal to =IF(OR(S22=$W$5,S22=$X$5,S22=$Y $5,S22=$Z$5,S22=$AA$5,S22=$AB$5),S22) Font = Black Border = Outline & Pattern Colour = Blue Condition 3 = Cell Value Is equal to =IF(S22=$AC$5,S22) Font = Black Border = Outline & Pattern Colour = Red Condition 2 & 3 work perfectly and if the cell is a zero it shows as empty which is great. The only thing it is not doing is that if the cell is greater than 0 and does not comply with condition 2 or 3 is make the Pattern colour grey and the border outline, it does put the right figure in that you can see. Any help will be greatly appreciated. Thanks in advance, Paul |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
if the
cell is greater than 0 and does not comply with condition 2 or 3 #1 If the cell is greater than 0 the first CF is applied and conditions 2 & 3 are *ignored* I'll pass on #2 #3 CF is applied to S22 =IF(S22=$AC$5,S22) Assuming CF 1&2 are false, CF3 is applied if S22 = AC5 and S22 when forced to a boolean evaluates as True Regards, Peter T "Paul Black" wrote in message ... Good afternoon, I have come up against a problem regarding conditional formatting. I have this formula in cell s22 ... '=IF(OR(G22<1,G2256)," ",IF(ISERROR(HLOOKUP(G22,$B$7:$AC $8,2,0)),HLOOKUP(G22,$B$9:$AC$10,2,0),HLOOKUP(G22, $B$7:$AC$8,2,0))) ... with the cells formated as ... Font = White No Border No Pattern I have ... Condition 1 = Cell Value Is greater than 0 Font = Black Border = Outline & Pattern Colour = Grey Condition 2 = Cell Value Is equal to =IF(OR(S22=$W$5,S22=$X$5,S22=$Y $5,S22=$Z$5,S22=$AA$5,S22=$AB$5),S22) Font = Black Border = Outline & Pattern Colour = Blue Condition 3 = Cell Value Is equal to =IF(S22=$AC$5,S22) Font = Black Border = Outline & Pattern Colour = Red Condition 2 & 3 work perfectly and if the cell is a zero it shows as empty which is great. The only thing it is not doing is that if the cell is greater than 0 and does not comply with condition 2 or 3 is make the Pattern colour grey and the border outline, it does put the right figure in that you can see. Any help will be greatly appreciated. Thanks in advance, Paul |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
On Oct 16, 3:58*pm, "Peter T" <peter_t@discussions wrote:
if the cell is greater than 0 and does not comply with condition 2 or 3 #1 If the cell is greater than 0 the first CF is applied and conditions 2 & 3 are *ignored* I'll pass on #2 #3 CF is applied to S22 =IF(S22=$AC$5,S22) Assuming CF 1&2 are false, CF3 is applied if S22 = AC5 and S22 when forced to a boolean evaluates as True Regards, Peter T "PaulBlack" wrote in message ... Good afternoon, I have come up against a problem regardingconditionalformatting. I have this formula in cell s22 ... '=IF(OR(G22<1,G2256)," ",IF(ISERROR(HLOOKUP(G22,$B$7:$AC $8,2,0)),HLOOKUP(G22,$B$9:$AC$10,2,0),HLOOKUP(G22, $B$7:$AC$8,2,0))) ... with the cells formated as ... Font = White No Border No Pattern I have ... Condition 1 = Cell Value Is greater than 0 Font = Black Border = Outline & Pattern Colour = Grey Condition 2 = Cell Value Is equal to =IF(OR(S22=$W$5,S22=$X$5,S22=$Y $5,S22=$Z$5,S22=$AA$5,S22=$AB$5),S22) Font = Black Border = Outline & Pattern Colour = Blue Condition 3 = Cell Value Is equal to =IF(S22=$AC$5,S22) Font = Black Border = Outline & Pattern Colour = Red Condition 2 & 3 work perfectly and if the cell is a zero it shows as empty which is great. The only thing it is not doing is that if the cell is greater than 0 and does not comply with condition 2 or 3 is make the Pattern colour grey and the border outline, it does put the right figure in that you can see. Any help will be greatly appreciated. Thanks in advance, Paul- Hide quoted text - - Show quoted text - Thanks for the reply Peter T. What I basically need is for ... Condition 1, if the cell is greater than zero or blank for it to be a grey background. Condition 2, if the cell is greater than zero or blank and complies with =IF(OR(S22=$W$5,S22=$X$5,S22=$Y $5,S22=$Z$5,S22=$AA$5,S22=$AB$5),S22) for the background to be blue. Condition 3, if the cell is greater than zero or blank and complies with =IF(S22=$AC$5,S22) for the background to be Red. Thanks in advance. Paul |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
"Paul Black" wrote in message " Thanks for the reply Peter T. What I basically need is for ... Condition 1, if the cell is greater than zero or blank for it to be a grey background. Condition 2, if the cell is greater than zero or blank and complies with =IF(OR(S22=$W$5,S22=$X$5,S22=$Y $5,S22=$Z$5,S22=$AA$5,S22=$AB$5),S22) for the background to be blue. Condition 3, if the cell is greater than zero or blank and complies with =IF(S22=$AC$5,S22) for the background to be Red. " CFs 2 & 3 will never kick in because CF1 will already have been applied on the basis of "greater than zero or blank" (btw simply =0 or "greater than or equal to: 0"). Sounds like you want to reorder your CFs. Decide which of CF2 or 3 has priority and make that the first CF, and change what is currently CF1 to CF3. Alternatively, make the default format same as current CF1 format, ie assume all values will be =0 (blank evaluates to zero). Change CF1 to "less than: 0" with the current default format. CFs 2 & 3 can remain pretty much as they are but you can remove the =0 check (handled by CF1's <0 check). That should make things simpler. Regards, Peter T |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
On Oct 19, 10:51*am, "Peter T" <peter_t@discussions wrote:
"Paul Black" wrote in message " Thanks for the reply Peter T. What I basically need is for ... Condition 1, if the cell is greater than zero or blank for it to be a grey background. Condition 2, if the cell is greater than zero or blank and complies with =IF(OR(S22=$W$5,S22=$X$5,S22=$Y $5,S22=$Z$5,S22=$AA$5,S22=$AB$5),S22) for the background to be blue.. Condition 3, if the cell is greater than zero or blank and complies with =IF(S22=$AC$5,S22) for the background to be Red. " CFs 2 & 3 will never kick in because CF1 will already have been applied on the basis of "greater than zero or blank" (btw simply *=0 or "greater than or equal to: 0"). Sounds like you want to reorder your CFs. Decide which of CF2 or 3 has priority and make that the first CF, and change what is currently CF1 to CF3. Alternatively, make the default format same as current CF1 format, ie assume all values will be *=0 (blank evaluates to zero). Change CF1 to "less than: 0" with the current default format. CFs 2 & 3 can remain pretty much as they are but you can remove the =0 check (handled by CF1's <0 check). That should make things simpler. Regards, Peter T Thanks for the help Peter I have now sorted it, Regards, Paul |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional formatting--different formatting depending on cell con | Excel Discussion (Misc queries) | |||
Formatting cells in a column with conditional formatting? | Excel Discussion (Misc queries) | |||
Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
Conditional Formatting that will display conditional data | Excel Worksheet Functions |