Conditional Formatting

[Paul Black]

Good afternoon,

I have come up against a problem regarding conditional formatting.
I have this formula in cell s22 ...

'=IF(OR(G22<1,G22>56)," ",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

 

[Pete_UK]

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

 

[Paul Black]

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













- 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

 

[David Biddulph]

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(S22>0,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(S22>0,S22=""),S22=$AC$5)

For grey (condition 3) you want =OR(S22>0,S22="")

 

[Paul Black]

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(S22>0,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(S22>0,S22=""),S22=$AC$5)

For grey (condition 3) you want =OR(S22>0,S22="")
--
David Biddulph











- Show quoted text -

Thanks David for your help I have now sorted it.

Regards,
Paul