unions, intersections or array constants
 

I am trying to make a conditional formatting formula in cell A3 that goes
something like this =$AK$2=JAN, MAR, MAY, JUL, SEP, NOV to format Blue and
=$AK$2=FEB, APR, JUN, AUG, OCT, DEC to format Green. It keeps coming up with
"You may not use unions, intersections or array constants for conditional
formatting criteria. Cell A3 and cell AK2 both have array fomulas. Is there a
solution to make this work?

unions, intersections or array constants
 

Use the Formula Is option and use these formulas:

For Blue:

=AND($AK$2<"",SEARCH($AK$2,"JANMARMAYJULSEPNOV"))

For Green:

=AND($AK$2<"",SEARCH($AK$2,"FEBAPRJUNAUGOCTDEC"))

--
Biff
Microsoft Excel MVP


"Loadmaster" wrote in message
...
I am trying to make a conditional formatting formula in cell A3 that goes
something like this =$AK$2=JAN, MAR, MAY, JUL, SEP, NOV to format Blue and
=$AK$2=FEB, APR, JUN, AUG, OCT, DEC to format Green. It keeps coming up
with
"You may not use unions, intersections or array constants for conditional
formatting criteria. Cell A3 and cell AK2 both have array fomulas. Is
there a
solution to make this work?

unions, intersections or array constants
 

One thing to watch out for in Biff's formulas... if your entries are not
well controlled, there is a remote possibility of getting false hits. For
example, if your user enters MarMay in AK2, the that cell will turn blue. As
structured, if your user types in any substring from "JANMARMAYJULSEPNOV"
(such as the MarMay I used) or from "FEBAPRJUNAUGOCTDEC", then those
substrings will be considered a hit.

--
Rick (MVP - Excel)


"T. Valko" wrote in message
...
Use the Formula Is option and use these formulas:

For Blue:

=AND($AK$2<"",SEARCH($AK$2,"JANMARMAYJULSEPNOV"))

For Green:

=AND($AK$2<"",SEARCH($AK$2,"FEBAPRJUNAUGOCTDEC"))

--
Biff
Microsoft Excel MVP


"Loadmaster" wrote in message
...
I am trying to make a conditional formatting formula in cell A3 that goes
something like this =$AK$2=JAN, MAR, MAY, JUL, SEP, NOV to format Blue
and
=$AK$2=FEB, APR, JUN, AUG, OCT, DEC to format Green. It keeps coming up
with
"You may not use unions, intersections or array constants for conditional
formatting criteria. Cell A3 and cell AK2 both have array fomulas. Is
there a
solution to make this work?

unions, intersections or array constants
 

If your entries are not well controlled and if you want to protect against
the problem I outlined in my previous posting, then these formulas should
work for you...

For Blue
===============
=AND($AK$2<"",$AK$2<"*",SEARCH("*"&$AK$2&"*","*J AN*MAR*MAY*JUL*SEP*NOV*"))

For Green
===============
=AND($AK$2<"",$AK$2<"*",SEARCH("*"&$AK$2&"*","*F EB*APR*JUN*AUG*OCT*DEC*"))

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
One thing to watch out for in Biff's formulas... if your entries are not
well controlled, there is a remote possibility of getting false hits. For
example, if your user enters MarMay in AK2, the that cell will turn blue.
As structured, if your user types in any substring from
"JANMARMAYJULSEPNOV" (such as the MarMay I used) or from
"FEBAPRJUNAUGOCTDEC", then those substrings will be considered a hit.

--
Rick (MVP - Excel)


"T. Valko" wrote in message
...
Use the Formula Is option and use these formulas:

For Blue:

=AND($AK$2<"",SEARCH($AK$2,"JANMARMAYJULSEPNOV"))

For Green:

=AND($AK$2<"",SEARCH($AK$2,"FEBAPRJUNAUGOCTDEC"))

--
Biff
Microsoft Excel MVP


"Loadmaster" wrote in message
...
I am trying to make a conditional formatting formula in cell A3 that goes
something like this =$AK$2=JAN, MAR, MAY, JUL, SEP, NOV to format Blue
and
=$AK$2=FEB, APR, JUN, AUG, OCT, DEC to format Green. It keeps coming up
with
"You may not use unions, intersections or array constants for
conditional
formatting criteria. Cell A3 and cell AK2 both have array fomulas. Is
there a
solution to make this work?

unions, intersections or array constants
 

Thank-you T.Valko you have never let me down on a formula, My only problem is
that excel 2003 only allows you to put three conditions. Here should be an
easy one for you. I am looking to combine a formula that if < 30 or 300 to
turn red. Presently I have them as two conditional formats in cell A5?

"T. Valko" wrote:

Use the Formula Is option and use these formulas:

For Blue:

=AND($AK$2<"",SEARCH($AK$2,"JANMARMAYJULSEPNOV"))

For Green:

=AND($AK$2<"",SEARCH($AK$2,"FEBAPRJUNAUGOCTDEC"))

--
Biff
Microsoft Excel MVP


"Loadmaster" wrote in message
...
I am trying to make a conditional formatting formula in cell A3 that goes
something like this =$AK$2=JAN, MAR, MAY, JUL, SEP, NOV to format Blue and
=$AK$2=FEB, APR, JUN, AUG, OCT, DEC to format Green. It keeps coming up
with
"You may not use unions, intersections or array constants for conditional
formatting criteria. Cell A3 and cell AK2 both have array fomulas. Is
there a
solution to make this work?

unions, intersections or array constants
 

Thank-you Rick, It worked also, I guess I will remember the day my
spreadsheet was completed on my 48th Birthday when the last conditional
format <30 or 300 is complete.

"Rick Rothstein" wrote:

If your entries are not well controlled and if you want to protect against
the problem I outlined in my previous posting, then these formulas should
work for you...

For Blue
===============
=AND($AK$2<"",$AK$2<"*",SEARCH("*"&$AK$2&"*","*J AN*MAR*MAY*JUL*SEP*NOV*"))

For Green
===============
=AND($AK$2<"",$AK$2<"*",SEARCH("*"&$AK$2&"*","*F EB*APR*JUN*AUG*OCT*DEC*"))

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
One thing to watch out for in Biff's formulas... if your entries are not
well controlled, there is a remote possibility of getting false hits. For
example, if your user enters MarMay in AK2, the that cell will turn blue.
As structured, if your user types in any substring from
"JANMARMAYJULSEPNOV" (such as the MarMay I used) or from
"FEBAPRJUNAUGOCTDEC", then those substrings will be considered a hit.

--
Rick (MVP - Excel)


"T. Valko" wrote in message
...
Use the Formula Is option and use these formulas:

For Blue:

=AND($AK$2<"",SEARCH($AK$2,"JANMARMAYJULSEPNOV"))

For Green:

=AND($AK$2<"",SEARCH($AK$2,"FEBAPRJUNAUGOCTDEC"))

--
Biff
Microsoft Excel MVP


"Loadmaster" wrote in message
...
I am trying to make a conditional formatting formula in cell A3 that goes
something like this =$AK$2=JAN, MAR, MAY, JUL, SEP, NOV to format Blue
and
=$AK$2=FEB, APR, JUN, AUG, OCT, DEC to format Green. It keeps coming up
with
"You may not use unions, intersections or array constants for
conditional
formatting criteria. Cell A3 and cell AK2 both have array fomulas. Is
there a
solution to make this work?

unions, intersections or array constants
 

Hey, I figured this one out myself. If cell value is not between 30 and 300
to format red. Thanks for all your help

"Loadmaster" wrote:

Thank-you T.Valko you have never let me down on a formula, My only problem is
that excel 2003 only allows you to put three conditions. Here should be an
easy one for you. I am looking to combine a formula that if < 30 or 300 to
turn red. Presently I have them as two conditional formats in cell A5?

"T. Valko" wrote:

Use the Formula Is option and use these formulas:

For Blue:

=AND($AK$2<"",SEARCH($AK$2,"JANMARMAYJULSEPNOV"))

For Green:

=AND($AK$2<"",SEARCH($AK$2,"FEBAPRJUNAUGOCTDEC"))

--
Biff
Microsoft Excel MVP


"Loadmaster" wrote in message
...
I am trying to make a conditional formatting formula in cell A3 that goes
something like this =$AK$2=JAN, MAR, MAY, JUL, SEP, NOV to format Blue and
=$AK$2=FEB, APR, JUN, AUG, OCT, DEC to format Green. It keeps coming up
with
"You may not use unions, intersections or array constants for conditional
formatting criteria. Cell A3 and cell AK2 both have array fomulas. Is
there a
solution to make this work?

unions, intersections or array constants
 

if your user enters MarMay in AK2

I wonder what the odds of that happening are? We don't even know if these
are user entered, they may be formula results.

--
Biff
Microsoft Excel MVP


"Rick Rothstein" wrote in message
...
One thing to watch out for in Biff's formulas... if your entries are not
well controlled, there is a remote possibility of getting false hits. For
example, if your user enters MarMay in AK2, the that cell will turn blue.
As structured, if your user types in any substring from
"JANMARMAYJULSEPNOV" (such as the MarMay I used) or from
"FEBAPRJUNAUGOCTDEC", then those substrings will be considered a hit.

--
Rick (MVP - Excel)


"T. Valko" wrote in message
...
Use the Formula Is option and use these formulas:

For Blue:

=AND($AK$2<"",SEARCH($AK$2,"JANMARMAYJULSEPNOV"))

For Green:

=AND($AK$2<"",SEARCH($AK$2,"FEBAPRJUNAUGOCTDEC"))

--
Biff
Microsoft Excel MVP


"Loadmaster" wrote in message
...
I am trying to make a conditional formatting formula in cell A3 that goes
something like this =$AK$2=JAN, MAR, MAY, JUL, SEP, NOV to format Blue
and
=$AK$2=FEB, APR, JUN, AUG, OCT, DEC to format Green. It keeps coming up
with
"You may not use unions, intersections or array constants for
conditional
formatting criteria. Cell A3 and cell AK2 both have array fomulas. Is
there a
solution to make this work?

unions, intersections or array constants
 

If your entries are not well controlled and if you want to protect against
the problem I outlined in my previous posting, then these formulas should
work for you...

Or not. I only tried to break the first one but the second one will break
just as well.

If AK2 = Jan*Mar*May the format is applied. So, what do you think the odds
of that happening are?

--
Biff
Microsoft Excel MVP


"Rick Rothstein" wrote in message
...
If your entries are not well controlled and if you want to protect against
the problem I outlined in my previous posting, then these formulas should
work for you...

For Blue
===============
=AND($AK$2<"",$AK$2<"*",SEARCH("*"&$AK$2&"*","*J AN*MAR*MAY*JUL*SEP*NOV*"))

For Green
===============
=AND($AK$2<"",$AK$2<"*",SEARCH("*"&$AK$2&"*","*F EB*APR*JUN*AUG*OCT*DEC*"))

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
One thing to watch out for in Biff's formulas... if your entries are not
well controlled, there is a remote possibility of getting false hits. For
example, if your user enters MarMay in AK2, the that cell will turn blue.
As structured, if your user types in any substring from
"JANMARMAYJULSEPNOV" (such as the MarMay I used) or from
"FEBAPRJUNAUGOCTDEC", then those substrings will be considered a hit.

--
Rick (MVP - Excel)


"T. Valko" wrote in message
...
Use the Formula Is option and use these formulas:

For Blue:

=AND($AK$2<"",SEARCH($AK$2,"JANMARMAYJULSEPNOV"))

For Green:

=AND($AK$2<"",SEARCH($AK$2,"FEBAPRJUNAUGOCTDEC"))

--
Biff
Microsoft Excel MVP


"Loadmaster" wrote in message
...
I am trying to make a conditional formatting formula in cell A3 that
goes
something like this =$AK$2=JAN, MAR, MAY, JUL, SEP, NOV to format Blue
and
=$AK$2=FEB, APR, JUN, AUG, OCT, DEC to format Green. It keeps coming up
with
"You may not use unions, intersections or array constants for
conditional
formatting criteria. Cell A3 and cell AK2 both have array fomulas. Is
there a
solution to make this work?

unions, intersections or array constants
 

if your user enters MarMay in AK2

I wonder what the odds of that happening are?

I used MarMay as but one possible example... any substring in your you
'search' string will cause a false positive... for example, the letter M by
itself.

We don't even know if these are user entered,
they may be formula results.

That is why I said "if your entries are not well controlled".

--
Rick (MVP - Excel)

unions, intersections or array constants
 

Good point! Maybe these formulas instead...

=AND($AK$2<"",NOT(ISNUMBER(FIND("*",$AK$2))),SEAR CH("*"&$AK$2&"*","*JAN*MAR*MAY*JUL*SEP*NOV*"))

=AND($AK$2<"",NOT(ISNUMBER(FIND("*",$AK$2))),SEAR CH("*"&$AK$2&"*","*FEB*APR*JUN*AUG*OCT*DEC*"))

--
Rick (MVP - Excel)


"T. Valko" wrote in message
...
If your entries are not well controlled and if you want to protect
against the problem I outlined in my previous posting, then these
formulas should work for you...

Or not. I only tried to break the first one but the second one will break
just as well.

If AK2 = Jan*Mar*May the format is applied. So, what do you think the odds
of that happening are?

--
Biff
Microsoft Excel MVP


"Rick Rothstein" wrote in message
...
If your entries are not well controlled and if you want to protect
against the problem I outlined in my previous posting, then these
formulas should work for you...

For Blue
===============
=AND($AK$2<"",$AK$2<"*",SEARCH("*"&$AK$2&"*","*J AN*MAR*MAY*JUL*SEP*NOV*"))

For Green
===============
=AND($AK$2<"",$AK$2<"*",SEARCH("*"&$AK$2&"*","*F EB*APR*JUN*AUG*OCT*DEC*"))

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
One thing to watch out for in Biff's formulas... if your entries are not
well controlled, there is a remote possibility of getting false hits.
For example, if your user enters MarMay in AK2, the that cell will turn
blue. As structured, if your user types in any substring from
"JANMARMAYJULSEPNOV" (such as the MarMay I used) or from
"FEBAPRJUNAUGOCTDEC", then those substrings will be considered a hit.

--
Rick (MVP - Excel)


"T. Valko" wrote in message
...
Use the Formula Is option and use these formulas:

For Blue:

=AND($AK$2<"",SEARCH($AK$2,"JANMARMAYJULSEPNOV"))

For Green:

=AND($AK$2<"",SEARCH($AK$2,"FEBAPRJUNAUGOCTDEC"))

--
Biff
Microsoft Excel MVP


"Loadmaster" wrote in message
...
I am trying to make a conditional formatting formula in cell A3 that
goes
something like this =$AK$2=JAN, MAR, MAY, JUL, SEP, NOV to format Blue
and
=$AK$2=FEB, APR, JUN, AUG, OCT, DEC to format Green. It keeps coming
up with
"You may not use unions, intersections or array constants for
conditional
formatting criteria. Cell A3 and cell AK2 both have array fomulas. Is
there a
solution to make this work?

unions, intersections or array constants
 

Rick Rothstein wrote:
Good point! Maybe these formulas instead...

=AND($AK$2<"",NOT(ISNUMBER(FIND("*",$AK$2))),SEAR CH("*"&$AK$2&"*","*JAN*MAR*MAY*JUL*SEP*NOV*"))


=AND($AK$2<"",NOT(ISNUMBER(FIND("*",$AK$2))),SEAR CH("*"&$AK$2&"*","*FEB*APR*JUN*AUG*OCT*DEC*"))




Why not something like this:

=ISODD(MONTH(DATEVALUE("1-"&$AK$2&"-2000")))

unions, intersections or array constants
 

=ISODD(MONTH(DATEVALUE("1-"&$AK$2&"-2000")))

ISODD uses the ATP. Excel will complain. I'd use MOD instead. But, I'd like
to know if these are formula results or user entered. I went under the
assumption they were formula results as they were presented in uppercase.

--
Biff
Microsoft Excel MVP


"Glenn" wrote in message
...
Rick Rothstein wrote:
Good point! Maybe these formulas instead...

=AND($AK$2<"",NOT(ISNUMBER(FIND("*",$AK$2))),SEAR CH("*"&$AK$2&"*","*JAN*MAR*MAY*JUL*SEP*NOV*"))
=AND($AK$2<"",NOT(ISNUMBER(FIND("*",$AK$2))),SEAR CH("*"&$AK$2&"*","*FEB*APR*JUN*AUG*OCT*DEC*"))


Why not something like this:

=ISODD(MONTH(DATEVALUE("1-"&$AK$2&"-2000")))

unions, intersections or array constants
 

=ISODD(MONTH(DATEVALUE("1-"&$AK$2&"-2000")))

If AK2 is a number the format is applied based on the month number of the
date serial number.


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
=ISODD(MONTH(DATEVALUE("1-"&$AK$2&"-2000")))

ISODD uses the ATP. Excel will complain. I'd use MOD instead. But, I'd
like to know if these are formula results or user entered. I went under
the assumption they were formula results as they were presented in
uppercase.

--
Biff
Microsoft Excel MVP


"Glenn" wrote in message
...
Rick Rothstein wrote:
Good point! Maybe these formulas instead...

=AND($AK$2<"",NOT(ISNUMBER(FIND("*",$AK$2))),SEAR CH("*"&$AK$2&"*","*JAN*MAR*MAY*JUL*SEP*NOV*"))
=AND($AK$2<"",NOT(ISNUMBER(FIND("*",$AK$2))),SEAR CH("*"&$AK$2&"*","*FEB*APR*JUN*AUG*OCT*DEC*"))


Why not something like this:

=ISODD(MONTH(DATEVALUE("1-"&$AK$2&"-2000")))

unions, intersections or array constants
 

If AK2 is a number...

....from 1 to 31...

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
=ISODD(MONTH(DATEVALUE("1-"&$AK$2&"-2000")))

If AK2 is a number the format is applied based on the month number of the
date serial number.


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
=ISODD(MONTH(DATEVALUE("1-"&$AK$2&"-2000")))

ISODD uses the ATP. Excel will complain. I'd use MOD instead. But, I'd
like to know if these are formula results or user entered. I went under
the assumption they were formula results as they were presented in
uppercase.

--
Biff
Microsoft Excel MVP


"Glenn" wrote in message
...
Rick Rothstein wrote:
Good point! Maybe these formulas instead...

=AND($AK$2<"",NOT(ISNUMBER(FIND("*",$AK$2))),SEAR CH("*"&$AK$2&"*","*JAN*MAR*MAY*JUL*SEP*NOV*"))
=AND($AK$2<"",NOT(ISNUMBER(FIND("*",$AK$2))),SEAR CH("*"&$AK$2&"*","*FEB*APR*JUN*AUG*OCT*DEC*"))


Why not something like this:

=ISODD(MONTH(DATEVALUE("1-"&$AK$2&"-2000")))

unions, intersections or array constants
 

"T. Valko" wrote...
Use the Formula Is option and use these formulas:

For Blue:

=AND($AK$2<"",SEARCH($AK$2,"JANMARMAYJULSEPNOV") )

For Green:

=AND($AK$2<"",SEARCH($AK$2,"FEBAPRJUNAUGOCTDEC") )
....

Quibble: these could return false positives for invalid entries in
cell AK2, e.g., if AK2 evaluated to "ARM", it'd be formatted blue.

Alternatives:

blue: =MOD(MONTH($AK$2&"-1"),2)=1

green: =MOD(MONTH($AK$1&"-1"),2)=0

unions, intersections or array constants
 

T. Valko wrote:
=ISODD(MONTH(DATEVALUE("1-"&$AK$2&"-2000")))

ISODD uses the ATP. Excel will complain. I'd use MOD instead. But, I'd like
to know if these are formula results or user entered. I went under the
assumption they were formula results as they were presented in uppercase.



Right, so some variation of this:

=MOD(MONTH(DATEVALUE("1-"&$AK$2&"-2000")),2)

The OP seems to be saying that the data in AK2 is text, but if not, than you
could drop the DATEVALUE() and I would just use something like this:

=MOD(MONTH($AK$2))

unions, intersections or array constants
 

Glenn wrote:
T. Valko wrote:
=ISODD(MONTH(DATEVALUE("1-"&$AK$2&"-2000")))

ISODD uses the ATP. Excel will complain. I'd use MOD instead. But, I'd
like to know if these are formula results or user entered. I went
under the assumption they were formula results as they were presented
in uppercase.



Right, so some variation of this:

=MOD(MONTH(DATEVALUE("1-"&$AK$2&"-2000")),2)

The OP seems to be saying that the data in AK2 is text, but if not, than
you could drop the DATEVALUE() and I would just use something like this:

=MOD(MONTH($AK$2))


Obviously missing the ",2" in that last MOD()...

=MOD(MONTH($AK$2),2)

unions, intersections or array constants
 

Quibble

This post has turned into a can-o-worms!

We (I) need to know if these are user entered or formula generated.

Alternatives:

If the cell contains a number from 1 to 12 one or the other formats is
applied.


--
Biff
Microsoft Excel MVP


"Harlan Grove" wrote in message
...
"T. Valko" wrote...
Use the Formula Is option and use these formulas:

For Blue:

=AND($AK$2<"",SEARCH($AK$2,"JANMARMAYJULSEPNOV" ))

For Green:

=AND($AK$2<"",SEARCH($AK$2,"FEBAPRJUNAUGOCTDEC" ))
...

Quibble: these could return false positives for invalid entries in
cell AK2, e.g., if AK2 evaluated to "ARM", it'd be formatted blue.

Alternatives:

blue: =MOD(MONTH($AK$2&"-1"),2)=1

green: =MOD(MONTH($AK$1&"-1"),2)=0

unions, intersections or array constants
 

Thanks for the info and all your help guys.

"T. Valko" wrote:

if your user enters MarMay in AK2

I wonder what the odds of that happening are? We don't even know if these
are user entered, they may be formula results.

--
Biff
Microsoft Excel MVP


"Rick Rothstein" wrote in message
...
One thing to watch out for in Biff's formulas... if your entries are not
well controlled, there is a remote possibility of getting false hits. For
example, if your user enters MarMay in AK2, the that cell will turn blue.
As structured, if your user types in any substring from
"JANMARMAYJULSEPNOV" (such as the MarMay I used) or from
"FEBAPRJUNAUGOCTDEC", then those substrings will be considered a hit.

--
Rick (MVP - Excel)


"T. Valko" wrote in message
...
Use the Formula Is option and use these formulas:

For Blue:

=AND($AK$2<"",SEARCH($AK$2,"JANMARMAYJULSEPNOV"))

For Green:

=AND($AK$2<"",SEARCH($AK$2,"FEBAPRJUNAUGOCTDEC"))

--
Biff
Microsoft Excel MVP


"Loadmaster" wrote in message
...
I am trying to make a conditional formatting formula in cell A3 that goes
something like this =$AK$2=JAN, MAR, MAY, JUL, SEP, NOV to format Blue
and
=$AK$2=FEB, APR, JUN, AUG, OCT, DEC to format Green. It keeps coming up
with
"You may not use unions, intersections or array constants for
conditional
formatting criteria. Cell A3 and cell AK2 both have array fomulas. Is
there a
solution to make this work?

unions, intersections or array constants
 

This ought to avoid false hits:
=AND(LEN($AK$2)=3,SEARCH($AK$2&".","JAN.MAR.MAY.JU L.SEP.NOV."))

The extra "." is to avoid false hits like "ARM".

One thing to watch out for in Biff's formulas... if your entries
are not well controlled, there is a remote possibility of getting
false hits. For example, if your user enters MarMay in AK2, the
that cell will turn blue. As structured, if your user types in any
substring from "JANMARMAYJULSEPNOV" (such as the MarMay I used) or
from "FEBAPRJUNAUGOCTDEC", then those substrings will be considered
a hit.

Use the Formula Is option and use these formulas:

For Blue:

=AND($AK$2<"",SEARCH($AK$2,"JANMARMAYJULSEPNOV"))

For Green:

=AND($AK$2<"",SEARCH($AK$2,"FEBAPRJUNAUGOCTDEC"))

I am trying to make a conditional formatting formula in cell A3
that goes
something like this =$AK$2=JAN, MAR, MAY, JUL, SEP, NOV to format
Blue ...

unions, intersections or array constants
 

T. Valko, to answer your questions on if these are user generated formulas is
you gave me the two array formulas. The one in A3 is:

=LOOKUP(9.999999999999E+307,INDEX(D2:AH13,0,MAX(IF (D2:AH13<"",COLUMN(D2:AH13)-COLUMN(D2)+1))))

and, the one in AK2 is:

=INDEX(C2:C13,MATCH(1E+100,INDEX(D2:AJ13,0,MAX(IF( D2:AJ13<"",COLUMN(D2:AJ13)-COLUMN(D2)+1)))))

"T. Valko" wrote:

Quibble

This post has turned into a can-o-worms!

We (I) need to know if these are user entered or formula generated.

Alternatives:

If the cell contains a number from 1 to 12 one or the other formats is
applied.


--
Biff
Microsoft Excel MVP


"Harlan Grove" wrote in message
...
"T. Valko" wrote...
Use the Formula Is option and use these formulas:

For Blue:

=AND($AK$2<"",SEARCH($AK$2,"JANMARMAYJULSEPNOV" ))

For Green:

=AND($AK$2<"",SEARCH($AK$2,"FEBAPRJUNAUGOCTDEC" ))
...

Quibble: these could return false positives for invalid entries in
cell AK2, e.g., if AK2 evaluated to "ARM", it'd be formatted blue.

Alternatives:

blue: =MOD(MONTH($AK$2&"-1"),2)=1

green: =MOD(MONTH($AK$1&"-1"),2)=0

unions, intersections or array constants
 

Ok, that helps to settle all of our "yeah, but if..." scenarios.

This went from a conditional formatting question to a data validation
contest.

--
Biff
Microsoft Excel MVP


"Loadmaster" wrote in message
...
T. Valko, to answer your questions on if these are user generated formulas
is
you gave me the two array formulas. The one in A3 is:

=LOOKUP(9.999999999999E+307,INDEX(D2:AH13,0,MAX(IF (D2:AH13<"",COLUMN(D2:AH13)-COLUMN(D2)+1))))

and, the one in AK2 is:

=INDEX(C2:C13,MATCH(1E+100,INDEX(D2:AJ13,0,MAX(IF( D2:AJ13<"",COLUMN(D2:AJ13)-COLUMN(D2)+1)))))

"T. Valko" wrote:

Quibble

This post has turned into a can-o-worms!

We (I) need to know if these are user entered or formula generated.

Alternatives:

If the cell contains a number from 1 to 12 one or the other formats is
applied.


--
Biff
Microsoft Excel MVP


"Harlan Grove" wrote in message
...
"T. Valko" wrote...
Use the Formula Is option and use these formulas:

For Blue:

=AND($AK$2<"",SEARCH($AK$2,"JANMARMAYJULSEPNOV" ))

For Green:

=AND($AK$2<"",SEARCH($AK$2,"FEBAPRJUNAUGOCTDEC" ))
...

Quibble: these could return false positives for invalid entries in
cell AK2, e.g., if AK2 evaluated to "ARM", it'd be formatted blue.

Alternatives:

blue: =MOD(MONTH($AK$2&"-1"),2)=1

green: =MOD(MONTH($AK$1&"-1"),2)=0

unions, intersections or array constants
 

"T. Valko" wrote...
....
Alternatives:

If the cell contains a number from 1 to 12 one or the other formats is
applied.
....

Good point.

blue: =IF(ISTEXT($AK$2),MOD(MONTH($AK$2&"-1"),2)=1)

green: =IF(ISTEXT($AK$2),MOD(MONTH($AK$2&"-1"),2)=0)

These allow whitespace in AK2. If that's not OK, then change the
second cell
references to SUBSTITUTE($AK$2," ","%").

You need to use an OR function: =OR($AK$2="JAN",$AK$2="MAR",$AK$2="MAY",$AK$2="JUL ",$AK$2="SEP",$AK$2="NOV")=TRUE that'll work for the Blue one, same idea for the green one.