| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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.misc
|
|||
|
|||
|
Conditional Formatting
For conditions 2 and 3 you will need to select Formula Is rather than
Cell Value Is in the first box in the CF dialogue, and put your formulae in the formula box. Hope this helps. Pete On Oct 16, 2:35*pm, Paul Black wrote: 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.misc
|
|||
|
|||
|
Conditional Formatting
On Oct 16, 4:11*pm, Pete_UK wrote:
For conditions 2 and 3 you will need to select Formula Is rather than Cell Value Is in the first box in the CF dialogue, and put your formulae in the formula box. Hope this helps. Pete On Oct 16, 2:35*pm,PaulBlack wrote: 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 Pete. 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.misc
|
|||
|
|||
|
Conditional Formatting
The formula for the condition should be something which returns TRUE or
FALSE. Instead of =IF(OR(S22=$W$5,S22=$X$5,S22=$Y$5,S22=$Z$5,S22=$AA $5,S22=$AB$5),S22) use =OR(S22=$W$5,S22=$X$5,S22=$Y$5,S22=$Z$5,S22=$AA$5, S22=$AB$5), so condition 1 (for blue) should be =AND(OR(S220,S22=""),OR(S22=$W$5,S22=$X$5,S22=$Y$ 5,S22=$Z$5,S22=$AA$5,S22=$AB$5)) Instead of =IF(S22=$AC$5,S22) use =S22=$AC$5, so condition 2 (for red) should be =AND(OR(S220,S22=""),S22=$AC$5) For grey (condition 3) you want =OR(S220,S22="") -- David Biddulph Paul Black wrote: Thanks for the reply Pete. 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 On Oct 16, 4:11 pm, Pete_UK wrote: For conditions 2 and 3 you will need to select Formula Is rather than Cell Value Is in the first box in the CF dialogue, and put your formulae in the formula box. Hope this helps. Pete On Oct 16, 2:35 pm,PaulBlack wrote: 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 - |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formatting
On Oct 19, 8:52*am, "David Biddulph" <groups [at] biddulph.org.uk
wrote: The formula for the condition should be something which returns TRUE or FALSE. Instead of =IF(OR(S22=$W$5,S22=$X$5,S22=$Y$5,S22=$Z$5,S22=$AA $5,S22=$AB$5),S22) use =OR(S22=$W$5,S22=$X$5,S22=$Y$5,S22=$Z$5,S22=$AA$5, S22=$AB$5), so condition 1 (for blue) should be =AND(OR(S220,S22=""),OR(S22=$W$5,S22=$X$5,S22=$Y$ 5,S22=$Z$5,S22=$AA$5,S22=*$AB$5)) Instead of =IF(S22=$AC$5,S22) use =S22=$AC$5, so condition 2 (for red) should be =AND(OR(S220,S22=""),S22=$AC$5) For grey (condition 3) you want =OR(S220,S22="") -- David Biddulph Paul Black wrote: Thanks for the reply Pete. 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 On Oct 16, 4:11 pm, Pete_UK wrote: For conditions 2 and 3 you will need to select Formula Is rather than Cell Value Is in the first box in the CF dialogue, and put your formulae in the formula box. Hope this helps. Pete On Oct 16, 2:35 pm,PaulBlack wrote: 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 -- Hide quoted text - - Show quoted text - Thanks David for your help I have now sorted it. Regards, Paul |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can I convert conditional formatting into explicit formatting? | Excel Discussion (Misc queries) | |||
| Conditional formatting--different formatting depending on cell con | Excel Discussion (Misc queries) | |||
| Formatting Conditional Formatting Icon Sets | Excel Discussion (Misc queries) | |||
| Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
| Conditional Formatting that will display conditional data | Excel Worksheet Functions | |||
Linear Mode
