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

 

[Peter T]

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]

#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
















- 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

 

[Peter T]

"
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

 

[Paul Black]

"
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