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

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)

In article <O#(E-Mail Removed)>,
"John Meyer" <jay(letter) dot meyer at makotec dot net> wrote:

> 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?

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

"John Meyer" <jay(letter) dot meyer at makotec dot net> wrote in message
news:O%(E-Mail Removed)...
> 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
>
>

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<>"")

--

Regards,

Peo Sjoblom

"John Meyer" <jay(letter) dot meyer at makotec dot net> wrote in message
news:O%(E-Mail Removed)...
> 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
>
>

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
"John Meyer" <jay(letter) dot meyer at makotec dot net> wrote in message
news:O#(E-Mail Removed)...
> 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
>
>

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

--

Regards,

Peo Sjoblom

"John Meyer" <jay(letter) dot meyer at makotec dot net> wrote in message
news:(E-Mail Removed)...
> 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
> "John Meyer" <jay(letter) dot meyer at makotec dot net> wrote in message
> news:O#(E-Mail Removed)...
> > 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
> >
> >
>
>

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

Your help has been great!

John


"Peo Sjoblom" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> "John Meyer" <jay(letter) dot meyer at makotec dot net> wrote in message
> news:(E-Mail Removed)...
> > 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
> > "John Meyer" <jay(letter) dot meyer at makotec dot net> wrote in message
> > news:O#(E-Mail Removed)...
> > > 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
> > >
> > >
> >
> >
>
>

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

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


--

Regards,

Peo Sjoblom

"John Meyer" <jay(letter) dot meyer at makotec dot net> wrote in message
news:(E-Mail Removed)...
> Thanks Peo (and to the other people who replied).
>
> Your help has been great!
>
> John
>
>
> "Peo Sjoblom" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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
> >
> > --
> >
> > Regards,
> >
> > Peo Sjoblom
> >
> > "John Meyer" <jay(letter) dot meyer at makotec dot net> wrote in message
> > news:(E-Mail Removed)...
> > > 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
> > > "John Meyer" <jay(letter) dot meyer at makotec dot net> wrote in
message
> > > news:O#(E-Mail Removed)...
> > > > 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
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Yeah, I fixed that myself.

But thanks for the reply!

John

"Peo Sjoblom" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Actually to guard against both empty cells and 0 this is probably better
>
> =AND(B3<=TODAY()+5,B3>=TODAY(),I3<>"",I3<>0)
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> "John Meyer" <jay(letter) dot meyer at makotec dot net> wrote in message
> news:(E-Mail Removed)...
> > Thanks Peo (and to the other people who replied).
> >
> > Your help has been great!
> >
> > John
> >
> >
> > "Peo Sjoblom" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > 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
> > >
> > > --
> > >
> > > Regards,
> > >
> > > Peo Sjoblom
> > >
> > > "John Meyer" <jay(letter) dot meyer at makotec dot net> wrote in
message
> > > news:(E-Mail Removed)...
> > > > 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
> > > > "John Meyer" <jay(letter) dot meyer at makotec dot net> wrote in
> message
> > > > news:O#(E-Mail Removed)...
> > > > > 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
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>