Conditional formatting similar to shading alternating rows

Hello all,

I'm trying to use the following formula in conditional formatting in cell A2 when the range A2:AW64 is selected (this conditional
formatting will be copied to all cells in the selected range):

Formula is: =isodd($A2)

The error I keep getting is this:

"You may not use references to other worksheets or other workbooks for Conditional Formatting criteria."

I'm trying to use this conditional formatting because column A is our "Order" column. "Order" can be from 1 to 13 and this column
is sorted ascending. There can be multiple rows in each Order number. So I want to shade all 1's, 3's, 5's, etc... so that each
Order group has alternating shading.

I also want to use conditional formatting, not VBA, to get this to work. In the future, rows might be added to an Order number or
two and I want the shading to automatically adjust.

What is going on here? Does anyone know how to get this to work?

--
Thanks for any help anyone can provide,

Conan Kelly

It may be because the ISodd function comes from the analysis toolpak

try formula is =mod(row(),2)=1


"Conan Kelly" wrote:

> Hello all,
>
> I'm trying to use the following formula in conditional formatting in cell A2 when the range A2:AW64 is selected (this conditional
> formatting will be copied to all cells in the selected range):
>
> Formula is: =isodd($A2)
>
> The error I keep getting is this:
>
> "You may not use references to other worksheets or other workbooks for Conditional Formatting criteria."
>
> I'm trying to use this conditional formatting because column A is our "Order" column. "Order" can be from 1 to 13 and this column
> is sorted ascending. There can be multiple rows in each Order number. So I want to shade all 1's, 3's, 5's, etc... so that each
> Order group has alternating shading.
>
> I also want to use conditional formatting, not VBA, to get this to work. In the future, rows might be added to an Order number or
> two and I want the shading to automatically adjust.
>
> What is going on here? Does anyone know how to get this to work?
>
> --
> Thanks for any help anyone can provide,
>
> Conan Kelly
>
>
>

bj,

Thanks for the feedback, but that won't accomplish what I'm trying to do.

your suggestion will shade alternating rows. I don't want alternating rows shaded, I want groups of rows shaded based on what the
value is in the first cell of that row. Please read the bottom part of my post for an explination.

Thanks again,

Conan


"bj" <[email protected]> wrote in message news:[email protected]...
> It may be because the ISodd function comes from the analysis toolpak
>
> try formula is =mod(row(),2)=1
>
>
> "Conan Kelly" wrote:
>
>> Hello all,
>>
>> I'm trying to use the following formula in conditional formatting in cell A2 when the range A2:AW64 is selected (this conditional
>> formatting will be copied to all cells in the selected range):
>>
>> Formula is: =isodd($A2)
>>
>> The error I keep getting is this:
>>
>> "You may not use references to other worksheets or other workbooks for Conditional Formatting criteria."
>>
>> I'm trying to use this conditional formatting because column A is our "Order" column. "Order" can be from 1 to 13 and this
>> column
>> is sorted ascending. There can be multiple rows in each Order number. So I want to shade all 1's, 3's, 5's, etc... so that each
>> Order group has alternating shading.
>>
>> I also want to use conditional formatting, not VBA, to get this to work. In the future, rows might be added to an Order number
>> or
>> two and I want the shading to automatically adjust.
>>
>> What is going on here? Does anyone know how to get this to work?
>>
>> --
>> Thanks for any help anyone can provide,
>>
>> Conan Kelly
>>
>>
>>

"Conan Kelly" <[email protected]> wrote in message
news:[email protected]...
> Hello all,
>
> I'm trying to use the following formula in conditional formatting in cell
> A2 when the range A2:AW64 is selected (this conditional formatting will be
> copied to all cells in the selected range):
>
> Formula is: =isodd($A2)
>
> The error I keep getting is this:
>
> "You may not use references to other worksheets or other workbooks for
> Conditional Formatting criteria."
>
> I'm trying to use this conditional formatting because column A is our
> "Order" column. "Order" can be from 1 to 13 and this column is sorted
> ascending. There can be multiple rows in each Order number. So I want to
> shade all 1's, 3's, 5's, etc... so that each Order group has alternating
> shading.
>
> I also want to use conditional formatting, not VBA, to get this to work.
> In the future, rows might be added to an Order number or two and I want
> the shading to automatically adjust.
>
> What is going on here? Does anyone know how to get this to work?
>
> --
> Thanks for any help anyone can provide,
>
> Conan Kelly

Conan,

Try Condition format / formula is / =MOD($A2,2)=0

Beege

"Beege" <[email protected]> wrote in message
news:[email protected]...
> "Conan Kelly" <[email protected]> wrote in message
> news:[email protected]...
>> Hello all,
>>
>> I'm trying to use the following formula in conditional formatting in cell
>> A2 when the range A2:AW64 is selected (this conditional formatting will
>> be copied to all cells in the selected range):
>>
>> Formula is: =isodd($A2)
>>
>> The error I keep getting is this:
>>
>> "You may not use references to other worksheets or other workbooks for
>> Conditional Formatting criteria."
>>
>> I'm trying to use this conditional formatting because column A is our
>> "Order" column. "Order" can be from 1 to 13 and this column is sorted
>> ascending. There can be multiple rows in each Order number. So I want
>> to shade all 1's, 3's, 5's, etc... so that each Order group has
>> alternating shading.
>>
>> I also want to use conditional formatting, not VBA, to get this to work.
>> In the future, rows might be added to an Order number or two and I want
>> the shading to automatically adjust.
>>
>> What is going on here? Does anyone know how to get this to work?
>>
>> --
>> Thanks for any help anyone can provide,
>>
>> Conan Kelly
>
> Conan,
>
> Try Condition format / formula is / > Beege

Conan,

That will shade even numbered orders

For Odd numbered, use =MOD($A2,2)<>0

MOD will give you the decimal part of A2 divided by that second parameter
(2)

Beege

Beege

Beege,

Thanks for all of the help. That worked beautifully. I guess that BJ was correct: you can't use functions from ads-ins in the
conditional formatting (<--can any one confirm that?).

Thanks again for all of your help,

Conan


"Beege" <[email protected]> wrote in message news:[email protected]...
> "Conan Kelly" <[email protected]> wrote in message news:[email protected]...
>> Hello all,
>>
>> I'm trying to use the following formula in conditional formatting in cell A2 when the range A2:AW64 is selected (this conditional
>> formatting will be copied to all cells in the selected range):
>>
>> Formula is: =isodd($A2)
>>
>> The error I keep getting is this:
>>
>> "You may not use references to other worksheets or other workbooks for Conditional Formatting criteria."
>>
>> I'm trying to use this conditional formatting because column A is our "Order" column. "Order" can be from 1 to 13 and this
>> column is sorted ascending. There can be multiple rows in each Order number. So I want to shade all 1's, 3's, 5's, etc... so
>> that each Order group has alternating shading.
>>
>> I also want to use conditional formatting, not VBA, to get this to work. In the future, rows might be added to an Order number or
>> two and I want the shading to automatically adjust.
>>
>> What is going on here? Does anyone know how to get this to work?
>>
>> --
>> Thanks for any help anyone can provide,
>>
>> Conan Kelly
>
> Conan,
>
> Try Condition format / formula is / =MOD($A2,2)=0
>
> Beege
>

You're welcome

"Conan Kelly" <[email protected]> wrote in message
news:[email protected]...
> Beege,
>
> Thanks for all of the help. That worked beautifully. I guess that BJ was
> correct: you can't use functions from ads-ins in the conditional
> formatting (<--can any one confirm that?).
>
> Thanks again for all of your help,
>
> Conan
>
>
> "Beege" <[email protected]> wrote in message
> news:[email protected]...
>> "Conan Kelly" <[email protected]> wrote in message
>> news:[email protected]...
>>> Hello all,
>>>
>>> I'm trying to use the following formula in conditional formatting in
>>> cell A2 when the range A2:AW64 is selected (this conditional formatting
>>> will be copied to all cells in the selected range):
>>>
>>> Formula is: =isodd($A2)
>>>
>>> The error I keep getting is this:
>>>
>>> "You may not use references to other worksheets or other workbooks for
>>> Conditional Formatting criteria."
>>>
>>> I'm trying to use this conditional formatting because column A is our
>>> "Order" column. "Order" can be from 1 to 13 and this column is sorted
>>> ascending. There can be multiple rows in each Order number. So I want
>>> to shade all 1's, 3's, 5's, etc... so that each Order group has
>>> alternating shading.
>>>
>>> I also want to use conditional formatting, not VBA, to get this to work.
>>> In the future, rows might be added to an Order number or two and I want
>>> the shading to automatically adjust.
>>>
>>> What is going on here? Does anyone know how to get this to work?
>>>
>>> --
>>> Thanks for any help anyone can provide,
>>>
>>> Conan Kelly
>>
>> Conan,
>>
>> Try Condition format / formula is / =MOD($A2,2)=0
>>
>> Beege
>>
>
>

> I guess that BJ was correct: you can't use functions from ads-ins in the
> conditional formatting (<--can any one confirm that?).

That is not what BJ said.

You can use your own functions in Conditional Formatting.
The functions in the Analysis Toolpak are not builtin, and
are like using your own functions, and using addins.

The problem with the Analysis Toolpak is that you must
turn them on with Tools, Addins, Analysis Toolpak
and not everyone is going to have them on. Memory
requirements would not be much of a consideration as
in the past.

Beege changed the formula to builtin functions rather than
forcing anyone to turn on the Analysis Toolpak, but
=MOD($A2,2)=0 produces TRUE for EVEN numbers

Conon (original poster) noted that the formula should be
=MOD($A2,2)=1 to work same as ISODD (for integers only)

The fact that you cannot reference cells outside the current worksheet,
may be what what was confusing you -- nothing to do with addins or the
Analysis Toolpak, and you can actually get around that
restriction by using named ranges .

My page on Conditional Formatting is
http://www.mvps.org/dmcritchie/excel/excel.htm

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Conan Kelly" <[email protected]> wrote in message
> I guess that BJ was correct: you can't use functions from ads-ins in the
> conditional formatting (<--can any one confirm that?).

>
> "Beege" <[email protected]> wrote ...
> > "Conan Kelly" <[email protected]> wrote
> >> I'm trying to use the following formula in conditional formatting in cell A2 when the range A2:AW64 is selected (this
conditional
> >> formatting will be copied to all cells in the selected range):
> >>
> >> Formula is: =isodd($A2)
> >>
> >> The error I keep getting is this:
> >>
> >> "You may not use references to other worksheets or other workbooks for Conditional Formatting criteria."
> >>
> >> I'm trying to use this conditional formatting because column A is our "Order" column. "Order" can be from 1 to 13 and this
> >> column is sorted ascending. There can be multiple rows in each Order number. So I want to shade all 1's, 3's, 5's, etc... so
> >> that each Order group has alternating shading.
> >>
> >> I also want to use conditional formatting, not VBA, to get this to work. In the future, rows might be added to an Order number
or
> >> two and I want the shading to automatically adjust.
> >>
> >> What is going on here? Does anyone know how to get this to work?
> >>
> >> --
> >> Thanks for any help anyone can provide,
> >>
> >> Conan Kelly

> > Conan,
> >
> > Try Condition format / formula is / =MOD($A2,2)=0
> >
> > Beege
> >
>
>

David,

Thanks for the feed back.

I was getting the message that I can't use external references, but I wasn't using external references. I was trying to reference
the cell I was applying the conditional formatting to though. For example, when working with the first row of data (A2:AW2), all of
these cells would have conditional formatting based on the value in A2 (no external references). But for what ever reason, I could
not use the ISODD or ISEVEN functions in conditional formatting. If I used a helper column (AX2: =ISODD(A2) or =ISEVEN(A2)), and
then used the reference to that helper column in the conditional formatting (=AX), everything worked fine.

Even though I was getting an External References error message, there were no external references involved. For some reason,
conditional formatting either doesn't like the ISODD/ISEVEN functions or doesn't like the formula referencing to the cell you are
trying to conditionally format or doesn't like the ISODD/ISEVEN functions referencing to the cell you are trying to conditionally
format. I'm not sure which one it is, but I couldn't get conditional formatting to work with ISODD/ISEVEN and referencing to the
cell I was trying to conditionally format.

Begee's suggestion using MOD referencing the cell I was trying to conditionally format worked just fine. So it likes MOD when
referencing itself, but it doesn't like ISODD/ISEVEN when referencing itself.

I hope what I'm saying make sense and isn't just nonsense rambling.

Thanks again for all of your help,

Conan



"David McRitchie" <[email protected]> wrote in message news:%[email protected]...
>> I guess that BJ was correct: you can't use functions from ads-ins in the
>> conditional formatting (<--can any one confirm that?).
>
> That is not what BJ said.
>
> You can use your own functions in Conditional Formatting.
> The functions in the Analysis Toolpak are not builtin, and
> are like using your own functions, and using addins.
>
> The problem with the Analysis Toolpak is that you must
> turn them on with Tools, Addins, Analysis Toolpak
> and not everyone is going to have them on. Memory
> requirements would not be much of a consideration as
> in the past.
>
> Beege changed the formula to builtin functions rather than
> forcing anyone to turn on the Analysis Toolpak, but
> =MOD($A2,2)=0 produces TRUE for EVEN numbers
>
> Conon (original poster) noted that the formula should be
> =MOD($A2,2)=1 to work same as ISODD (for integers only)
>
> The fact that you cannot reference cells outside the current worksheet,
> may be what what was confusing you -- nothing to do with addins or the
> Analysis Toolpak, and you can actually get around that
> restriction by using named ranges .
>
> My page on Conditional Formatting is
> http://www.mvps.org/dmcritchie/excel/excel.htm
>
> ---
> HTH,
> David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
> My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
> Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
>
> "Conan Kelly" <[email protected]> wrote in message
>> I guess that BJ was correct: you can't use functions from ads-ins in the
>> conditional formatting (<--can any one confirm that?).
>
>>
>> "Beege" <[email protected]> wrote ...
>> > "Conan Kelly" <[email protected]> wrote
>> >> I'm trying to use the following formula in conditional formatting in cell A2 when the range A2:AW64 is selected (this
> conditional
>> >> formatting will be copied to all cells in the selected range):
>> >>
>> >> Formula is: =isodd($A2)
>> >>
>> >> The error I keep getting is this:
>> >>
>> >> "You may not use references to other worksheets or other workbooks for Conditional Formatting criteria."
>> >>
>> >> I'm trying to use this conditional formatting because column A is our "Order" column. "Order" can be from 1 to 13 and this
>> >> column is sorted ascending. There can be multiple rows in each Order number. So I want to shade all 1's, 3's, 5's, etc... so
>> >> that each Order group has alternating shading.
>> >>
>> >> I also want to use conditional formatting, not VBA, to get this to work. In the future, rows might be added to an Order number
> or
>> >> two and I want the shading to automatically adjust.
>> >>
>> >> What is going on here? Does anyone know how to get this to work?
>> >>
>> >> --
>> >> Thanks for any help anyone can provide,
>> >>
>> >> Conan Kelly
>
>> > Conan,
>> >
>> > Try Condition format / formula is / =MOD($A2,2)=0
>> >
>> > Beege
>> >
>>
>>
>
>

Hi Conan,
I forgot that a user defined function had to be in the same
workbook, so that would apply to addins as well.
Could not find a reference to set in the VBE for the
Analysis Toolpak, but using MOD would be better
anyway.
---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Conan Kelly" <[email protected]> wrote
> I was getting the message that I can't use external references,
> but I wasn't using external references. I was trying to reference
> the cell I was applying the conditional formatting to though.

David

Most strange.

I have tried to find a reference to check in Tools>References so's I could use
the ATP functions in CF.

The closest I can get in FUNCRES.XLA if it is open(Tools>Add-ins>ATP)

But I still get the "Cannot use" message"

Don't have this problem with my own Add-ins.


Gord

On Tue, 22 Aug 2006 15:30:36 -0400, "David McRitchie"
<[email protected]> wrote:

>Hi Conan,
>I forgot that a user defined function had to be in the same
>workbook, so that would apply to addins as well.
>Could not find a reference to set in the VBE for the
>Analysis Toolpak, but using MOD would be better
>anyway.
>---
>HTH,
>David McRitchie, Microsoft MVP - Excel
>My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
>Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
>
>"Conan Kelly" <[email protected]> wrote
>> I was getting the message that I can't use external references,
>> but I wasn't using external references. I was trying to reference
>> the cell I was applying the conditional formatting to though.
>