Conditional, Conditional Formatting

[John Meyer]

Two questions, both have to do with semi-complex (at least for me)
conditional formatting.



1) I have two conditional formatting "formulas" on column B. Depending on
the corresponding row in column I, I want the conditional formatting on or
off. If I3 = 0, then don't allow conditional formatting for b3.



2) I want cells from column A to be "strikethrough" if the corresponding
cell in column I equals zero.



Is there any semi easy (from a data entry standpoint) way for this to be
accomplished?



Thanks,



John

 

[J.E. McGimpsey]

One way:

Say your current CF for column B is to format font color red if the
value in Bx is < 0. Select B3. Enter the CF:

CF1: Formula is =AND(B3<0, I3<>0)
Format: Font/Red

copy format as far as required


2) Select A3

CF1: Formula Is =I3=0
Format: Font/Strikethrough

copy format as far as required

Note that this will format Ax as strikethrough if Ix is blank, too.
if that's not desired, use this instead:

CF1: Formula is =IF(ISNUMBER(I3),I3=0)

 

[Dave Smith]

If I understand correctly, column B will be conditionally formatted where
column I doesn't equal 0; column A will be strikethrough where column I does
equal 0. I'm not sure why you have two conditional formats on B as I think
only one is needed for this. Presuming that I do understand what you want,
try the following:

Highlight column B, format > conditional formating, and choose "formula is"
in the drop down. Set the formula to this:
=(I1<>0)

Highlight column A, format > conditional formating, and choose "formula is"
in the drop down. Set the formula to this:
=AND((I1=0),NOT(ISBLANK(I1)))

Since blanks normally evaluate to 0, I have set up the second formula to
ignore them. If this is not what you want use this formula instead:
=(I1=0)

HTH

-Dave Smith

 

[Peo Sjoblom]

1. Assuming you are using formula is, add a constraint to the formula
for simplicity assume you have this formula in the formula is in B2

=I4>5

now change to

=AND(I4>5,I3<>0,I3<>"")

do that for both conditions and replace I4>5 with your conditions

2.

=AND(I4=0,I4<>"")

 

[John Meyer]

I didn't explain myself on part 1 well enough...

I currently have these conditional formats:
1 - cell value is between (today())+5 and today()
2 - cell value is between (today())-5 and today()-1

The first one formats the cell orange, second formats the cell red.

I do not want these conditions to apply on a cell where the corresponding
cell in column I is 0.

Is this possible?

Thanks again,

John

 

[Peo Sjoblom]

The only thing you have to do instead of using cell value is using formula
is, then use

=AND(B3<=TODAY()+5,B3>=TODAY(),I3<>"")


second formula I'll leave up to you

 

[John Meyer]

Thanks Peo (and to the other people who replied).

Your help has been great!

John

 

[Peo Sjoblom]

Actually to guard against both empty cells and 0 this is probably better

=AND(B3<=TODAY()+5,B3>=TODAY(),I3<>"",I3<>0)

 

[John Meyer]

Yeah, I fixed that myself.

But thanks for the reply!

John