Conditional Formatting, Multiple Criteria

First, please pardon my absolute inability to properly explain what I am
about to ask. I know next to nothing (or less) about programming.

I am trying to find out if I can make one cell do conditional formatting,
based on five different sets of criteria. Currently, it is set at the
following (please forgive "layman's terms"):

If F21 is between IU26 and IV26, then change cell and font to green.

This works fine.

What I need it to be able to do is perform the conditional formatting based
on one of the following five sets of criteria:


If F19 is between J29 and K29 and if F21 > K31...
or
If F19 is between K29 and L29 and if F21 > L31…
or
If F19 is between L29 and M29 and if F21 > N31…
or
If F19 is between M29 and N29 and if F21 > O31…
or
If F19>N29 and if F21 > P31…

… then change cell and font to green.


Is this even possible?? And if so, how do I go about making it so?

--
Lara

Change your Conditional Format from "Cell Value Is" to "Formula Is", then
enter this formula:

=OR(AND(F19>J29,F19<K29,F21>K31),AND(F19>K29,F19<L29,F21>L31),AND(F19>L29,F19<M29,F21>N31),AND(F19>M29,F19<N29,F21>O31),AND(F19>N29,F21>P31))

I think this accomplishes what you're looking for.

HTH,
Elkar


"Lara" wrote:

> First, please pardon my absolute inability to properly explain what I am
> about to ask. I know next to nothing (or less) about programming.
>
> I am trying to find out if I can make one cell do conditional formatting,
> based on five different sets of criteria. Currently, it is set at the
> following (please forgive "layman's terms"):
>
> If F21 is between IU26 and IV26, then change cell and font to green.
>
> This works fine.
>
> What I need it to be able to do is perform the conditional formatting based
> on one of the following five sets of criteria:
>
>
> If F19 is between J29 and K29 and if F21 > K31...
> or
> If F19 is between K29 and L29 and if F21 > L31…
> or
> If F19 is between L29 and M29 and if F21 > N31…
> or
> If F19 is between M29 and N29 and if F21 > O31…
> or
> If F19>N29 and if F21 > P31…
>
> … then change cell and font to green.
>
>
> Is this even possible?? And if so, how do I go about making it so?
>
> --
> Lara

Thank you, sorry, didn't see your post. I am having IE issues today... and
ended up posting my original question multiple times.

Thank you very much for your help!!

--
Lara


"Elkar" wrote:

> Change your Conditional Format from "Cell Value Is" to "Formula Is", then
> enter this formula:
>
> =OR(AND(F19>J29,F19<K29,F21>K31),AND(F19>K29,F19<L29,F21>L31),AND(F19>L29,F19<M29,F21>N31),AND(F19>M29,F19<N29,F21>O31),AND(F19>N29,F21>P31))
>
> I think this accomplishes what you're looking for.
>
> HTH,
> Elkar
>
>
> "Lara" wrote:
>
> > First, please pardon my absolute inability to properly explain what I am
> > about to ask. I know next to nothing (or less) about programming.
> >
> > I am trying to find out if I can make one cell do conditional formatting,
> > based on five different sets of criteria. Currently, it is set at the
> > following (please forgive "layman's terms"):
> >
> > If F21 is between IU26 and IV26, then change cell and font to green.
> >
> > This works fine.
> >
> > What I need it to be able to do is perform the conditional formatting based
> > on one of the following five sets of criteria:
> >
> >
> > If F19 is between J29 and K29 and if F21 > K31...
> > or
> > If F19 is between K29 and L29 and if F21 > L31…
> > or
> > If F19 is between L29 and M29 and if F21 > N31…
> > or
> > If F19 is between M29 and N29 and if F21 > O31…
> > or
> > If F19>N29 and if F21 > P31…
> >
> > … then change cell and font to green.
> >
> >
> > Is this even possible?? And if so, how do I go about making it so?
> >
> > --
> > Lara

Well, I gave this a shot, but I don't think it is looking at each "section"
of criteria. It's pulling parts of all arguments, not just one.

Basically, I want it to look at the first critera section and if it doesn't
fully match, pass it by to check the next entire critera section, and so on,
until it either finds one that all the conditions are met, or not at all.
"All of this" or "all of this" or "all of this," etc.
--
Lara


"Elkar" wrote:

> Change your Conditional Format from "Cell Value Is" to "Formula Is", then
> enter this formula:
>
> =OR(AND(F19>J29,F19<K29,F21>K31),AND(F19>K29,F19<L29,F21>L31),AND(F19>L29,F19<M29,F21>N31),AND(F19>M29,F19<N29,F21>O31),AND(F19>N29,F21>P31))
>
> I think this accomplishes what you're looking for.
>
> HTH,
> Elkar
>
>
> "Lara" wrote:
>
> > First, please pardon my absolute inability to properly explain what I am
> > about to ask. I know next to nothing (or less) about programming.
> >
> > I am trying to find out if I can make one cell do conditional formatting,
> > based on five different sets of criteria. Currently, it is set at the
> > following (please forgive "layman's terms"):
> >
> > If F21 is between IU26 and IV26, then change cell and font to green.
> >
> > This works fine.
> >
> > What I need it to be able to do is perform the conditional formatting based
> > on one of the following five sets of criteria:
> >
> >
> > If F19 is between J29 and K29 and if F21 > K31...
> > or
> > If F19 is between K29 and L29 and if F21 > L31…
> > or
> > If F19 is between L29 and M29 and if F21 > N31…
> > or
> > If F19 is between M29 and N29 and if F21 > O31…
> > or
> > If F19>N29 and if F21 > P31…
> >
> > … then change cell and font to green.
> >
> >
> > Is this even possible?? And if so, how do I go about making it so?
> >
> > --
> > Lara

Sorry, this did work, didn't realize I wasn't being specific enough in the
data IN those cells ("between" doesn't "include")! Thanks again...
--
Lara


"Lara" wrote:

> Well, I gave this a shot, but I don't think it is looking at each "section"
> of criteria. It's pulling parts of all arguments, not just one.
>
> Basically, I want it to look at the first critera section and if it doesn't
> fully match, pass it by to check the next entire critera section, and so on,
> until it either finds one that all the conditions are met, or not at all.
> "All of this" or "all of this" or "all of this," etc.
> --
> Lara
>
>
> "Elkar" wrote:
>
> > Change your Conditional Format from "Cell Value Is" to "Formula Is", then
> > enter this formula:
> >
> > =OR(AND(F19>J29,F19<K29,F21>K31),AND(F19>K29,F19<L29,F21>L31),AND(F19>L29,F19<M29,F21>N31),AND(F19>M29,F19<N29,F21>O31),AND(F19>N29,F21>P31))
> >
> > I think this accomplishes what you're looking for.
> >
> > HTH,
> > Elkar
> >
> >
> > "Lara" wrote:
> >
> > > First, please pardon my absolute inability to properly explain what I am
> > > about to ask. I know next to nothing (or less) about programming.
> > >
> > > I am trying to find out if I can make one cell do conditional formatting,
> > > based on five different sets of criteria. Currently, it is set at the
> > > following (please forgive "layman's terms"):
> > >
> > > If F21 is between IU26 and IV26, then change cell and font to green.
> > >
> > > This works fine.
> > >
> > > What I need it to be able to do is perform the conditional formatting based
> > > on one of the following five sets of criteria:
> > >
> > >
> > > If F19 is between J29 and K29 and if F21 > K31...
> > > or
> > > If F19 is between K29 and L29 and if F21 > L31…
> > > or
> > > If F19 is between L29 and M29 and if F21 > N31…
> > > or
> > > If F19 is between M29 and N29 and if F21 > O31…
> > > or
> > > If F19>N29 and if F21 > P31…
> > >
> > > … then change cell and font to green.
> > >
> > >
> > > Is this even possible?? And if so, how do I go about making it so?
> > >
> > > --
> > > Lara

It seems to work ok for me. But maybe I'm not understanding exactly what you
want. This formula does look at 5 individual groups of criteria, and if one
of those groups returns TRUE, then the format is applied.

So, in this formula, there are 5 groups of criteria:

1. F19>J29 F19<K29 F21>K31
2. F19>K29 F19<L29 F21>L31
3. F19>L29 F19<M29 F21>N31
4. F19>M29 F19<N29 F21>O31
5. F19>N29 F21>P31

If any one of those 5 groups is all TRUE, then the format is applied. If
its not working, then I think you might need to adjust your criteria a bit.
Make sure all cell references are accurate, and all < > symbols are correct.

HTH,
Elkar


"Lara" wrote:

> Well, I gave this a shot, but I don't think it is looking at each "section"
> of criteria. It's pulling parts of all arguments, not just one.
>
> Basically, I want it to look at the first critera section and if it doesn't
> fully match, pass it by to check the next entire critera section, and so on,
> until it either finds one that all the conditions are met, or not at all.
> "All of this" or "all of this" or "all of this," etc.
> --
> Lara
>
>
> "Elkar" wrote:
>
> > Change your Conditional Format from "Cell Value Is" to "Formula Is", then
> > enter this formula:
> >
> > =OR(AND(F19>J29,F19<K29,F21>K31),AND(F19>K29,F19<L29,F21>L31),AND(F19>L29,F19<M29,F21>N31),AND(F19>M29,F19<N29,F21>O31),AND(F19>N29,F21>P31))
> >
> > I think this accomplishes what you're looking for.
> >
> > HTH,
> > Elkar
> >
> >
> > "Lara" wrote:
> >
> > > First, please pardon my absolute inability to properly explain what I am
> > > about to ask. I know next to nothing (or less) about programming.
> > >
> > > I am trying to find out if I can make one cell do conditional formatting,
> > > based on five different sets of criteria. Currently, it is set at the
> > > following (please forgive "layman's terms"):
> > >
> > > If F21 is between IU26 and IV26, then change cell and font to green.
> > >
> > > This works fine.
> > >
> > > What I need it to be able to do is perform the conditional formatting based
> > > on one of the following five sets of criteria:
> > >
> > >
> > > If F19 is between J29 and K29 and if F21 > K31...
> > > or
> > > If F19 is between K29 and L29 and if F21 > L31…
> > > or
> > > If F19 is between L29 and M29 and if F21 > N31…
> > > or
> > > If F19 is between M29 and N29 and if F21 > O31…
> > > or
> > > If F19>N29 and if F21 > P31…
> > >
> > > … then change cell and font to green.
> > >
> > >
> > > Is this even possible?? And if so, how do I go about making it so?
> > >
> > > --
> > > Lara