I need two conditional formatting formulas with the
following criteria:

The first formula:
B30 is not blank
And
B30 is between M18 and M20, or, between M24 and M27

The second formula:
B30 is not blank
And
B30 is greater than or equal to M18, or, less than or
equal to M24

Thanks, Phil

Between means inclusive:
=AND(B30<"",OR(AND(B30=M18,B30=<M20),AND(B30=M2 4,B30=<M27)))
or not inclusive:
=AND(B30<"",OR(AND(B30M18,B30<M20),AND(B30M24,B 30<M27)))

2nd:
=And(B30<"".OR(B30=M18,B30<=M24))

--
Regards,
Tom Ogilvy



"Phil Hageman" wrote in message
...
I need two conditional formatting formulas with the
following criteria:

The first formula:
B30 is not blank
And
B30 is between M18 and M20, or, between M24 and M27

The second formula:
B30 is not blank
And
B30 is greater than or equal to M18, or, less than or
equal to M24

Thanks, Phil

On Tue, 9 Dec 2003 03:57:36 -0800, "Phil Hageman"
wrote:

I need two conditional formatting formulas with the
following criteria:

The first formula:
B30 is not blank
And
B30 is between M18 and M20, or, between M24 and M27

The second formula:
B30 is not blank
And
B30 is greater than or equal to M18, or, less than or
equal to M24

Thanks, Phil

I think the way you have written the specifications, checking for B30 to be
"not blank" is superfluous.

=OR(AND(B30M18,B30<M20),AND(B30M24,B30<M27))
does the rest.

Note that you specified "between". So if M18=2 and M20=5, neither 2 nor 5
would be "between".

That being the case, the testing of B30 for not being blank is unnecessary.


--ron

Select the range that you want formatting, then select
Format/conditional formating, select Formula Is then
enter:

=AND($B$30<"",OR(AND($B$30$M$18,$B$30<$M$20),AND
($B$30$M$24,$B$30<$M$27)))

As thsi is a code grouP:

Sub SetCF()

With Range("C7:G15")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:= "=AND($B$30<"""", _
OR(AND($B$30$M$18,$B$30<$M$20), _
AND($B$30$M$24,$B$30<$M$27)))"
.FormatConditions(1).Interior.ColorIndex = 34
End With
End Sub



Patrick Molloy
Microsoft Excel MVP

-----Original Message-----
I need two conditional formatting formulas with the
following criteria:

The first formula:
B30 is not blank
And
B30 is between M18 and M20, or, between M24 and M27

The second formula:
B30 is not blank
And
B30 is greater than or equal to M18, or, less than
or
equal to M24

Thanks, Phil
.

I tried to save you time by providing only part of the
problem, and ended up wasting your time instead - Sorry.
I couldn't get the solution formulas to work as needed so
here are the exact details.

Intent:
Color format the background of cell M30 based on its value:
1.) Green when: <M20 and M24
2.) Yellow when: <M18 and =M20, or, <=M24 and M27
3.) Red when: =M18 or <=M27
4.) If B30 is blank, no format applied - defaults to white

Values:

----- Red -----
M18 100%
---- Yellow ----
M20 95%
----- Green -----
M24 85%
---- Yellow ----
M27 80%
----- Red -----

B30 Value, a percent, determined by formula.

Original Message-----
Between means inclusive:
=AND(B30<"",OR(AND(B30=M18,B30=<M20),AND
(B30=M24,B30=<M27)))
or not inclusive:
=AND(B30<"",OR(AND(B30M18,B30<M20),AND
(B30M24,B30<M27)))

2nd:
=And(B30<"".OR(B30=M18,B30<=M24))

--
Regards,
Tom Ogilvy



"Phil Hageman"
wrote in message
...
I need two conditional formatting formulas with the
following criteria:

The first formula:
B30 is not blank
And
B30 is between M18 and M20, or, between M24 and M27

The second formula:
B30 is not blank
And
B30 is greater than or equal to M18, or, less than
or
equal to M24

Thanks, Phil


.

Change cell value is to Formula is:

=And(M30<M20,M30M24)

=OR(And(M30<M18,M30=M20),And(M30<=M24,M30M27))

=And(M30=M18,M30<=M27)

--
Regards,
Tom Ogilvy

"Phil Hageman" wrote in message
...
I tried to save you time by providing only part of the
problem, and ended up wasting your time instead - Sorry.
I couldn't get the solution formulas to work as needed so
here are the exact details.

Intent:
Color format the background of cell M30 based on its value:
1.) Green when: <M20 and M24
2.) Yellow when: <M18 and =M20, or, <=M24 and M27
3.) Red when: =M18 or <=M27
4.) If B30 is blank, no format applied - defaults to white

Values:

----- Red -----
M18 100%
---- Yellow ----
M20 95%
----- Green -----
M24 85%
---- Yellow ----
M27 80%
----- Red -----

B30 Value, a percent, determined by formula.

Original Message-----
Between means inclusive:
=AND(B30<"",OR(AND(B30=M18,B30=<M20),AND
(B30=M24,B30=<M27)))
or not inclusive:
=AND(B30<"",OR(AND(B30M18,B30<M20),AND
(B30M24,B30<M27)))

2nd:
=And(B30<"".OR(B30=M18,B30<=M24))

--
Regards,
Tom Ogilvy



"Phil Hageman"
wrote in message
...
I need two conditional formatting formulas with the
following criteria:

The first formula:
B30 is not blank
And
B30 is between M18 and M20, or, between M24 and M27

The second formula:
B30 is not blank
And
B30 is greater than or equal to M18, or, less than
or
equal to M24

Thanks, Phil


.

Error on last formula:

Change cell value is to Formula is:

=And(M30<M20,M30M24)

=OR(And(M30<M18,M30=M20),And(M30<=M24,M30M27))

=OR(M30=M18,M30<=M27)

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
Change cell value is to Formula is:

=And(M30<M20,M30M24)

=OR(And(M30<M18,M30=M20),And(M30<=M24,M30M27))

=And(M30=M18,M30<=M27)

--
Regards,
Tom Ogilvy

"Phil Hageman" wrote in message
...
I tried to save you time by providing only part of the
problem, and ended up wasting your time instead - Sorry.
I couldn't get the solution formulas to work as needed so
here are the exact details.

Intent:
Color format the background of cell M30 based on its value:
1.) Green when: <M20 and M24
2.) Yellow when: <M18 and =M20, or, <=M24 and M27
3.) Red when: =M18 or <=M27
4.) If B30 is blank, no format applied - defaults to white

Values:

----- Red -----
M18 100%
---- Yellow ----
M20 95%
----- Green -----
M24 85%
---- Yellow ----
M27 80%
----- Red -----

B30 Value, a percent, determined by formula.

Original Message-----
Between means inclusive:
=AND(B30<"",OR(AND(B30=M18,B30=<M20),AND
(B30=M24,B30=<M27)))
or not inclusive:
=AND(B30<"",OR(AND(B30M18,B30<M20),AND
(B30M24,B30<M27)))

2nd:
=And(B30<"".OR(B30=M18,B30<=M24))

--
Regards,
Tom Ogilvy



"Phil Hageman"
wrote in message
...
I need two conditional formatting formulas with the
following criteria:

The first formula:
B30 is not blank
And
B30 is between M18 and M20, or, between M24 and M27

The second formula:
B30 is not blank
And
B30 is greater than or equal to M18, or, less than
or
equal to M24

Thanks, Phil


.