conditional formating with isodd and iseven

[BIG D]

I have consective numbers in column A. In column B is formula =isodd
which returns True or False, and this works for me but I have tried to
use =isodd and =iseven as a conditional format formula with no luck.
My question is, is it possible to use either of these arguments in a
conditional format formula. I have tried severial syntax with no luck.
I most often get error message "May not use referance to other sheet
or book...." I know you guys can crack this.

Regards

BigD

 

[T. Valko]

I most often get error message "May not use referance to other sheet or

book...."

That's because those functions are part of the Analysis ToolPak add-in which
is another file (workbook).

For even numbers:

=MOD(A1,2)=0

For odd numbers:

=MOD(A2,2)>0

 

[carlo]

I don't know the formula isodd, but you can use this formula:
=MOD(A1,2)=1
which gives you false for any even and a true for any odd number.

hth

Carlo

 

[David Biddulph]

Do you need the =1, Carlo? Can't you just use =MOD(A1,2) ?

 

[T. Valko]

Can't you just use =MOD(A1,2) ?

Yes, you could. I included the >0 in my reply just to show some uniformity
with the even numbers formula.

=MOD(A1,2)=0
=MOD(A1,2)>0

 

[BIG D]

Can't you just use =MOD(A1,2) ?

Yes, you could. I included the >0 in my reply just to show some uniformity
with the even numbers formula.

=MOD(A1,2)=0
=MOD(A1,2)>0

--
Biff
Microsoft Excel MVP

"David Biddulph" <groups [at] biddulph.org.uk> wrote in message

Do you need the =1, Carlo? Can't you just use =MOD(A1,2) ?

- Show quoted text -

Thanks everyone! The formulas work and solve the problem nicely.
In response to Valko I do have the analysis tool pack installed on all
of my machines which makes me wonder if the isodd command is for some
reason not available in conditional formating. I do not know enough
about excel to test the command in cf but I am researching it.

Regards

BigD

 

[Peo Sjoblom]

Since ATP is an add-in it exists physically on another sheet and you cannot
refer to other sheets when doing CF unless you use a defined name. You can
bypass ATP by putting ISODD in another cell hidden from view, or even better
in another sheet, for instance if the cell you want to CF is in Sheet1

=ISODD(Sheet1!A1)

if you put that in another sheet and call that cell for ODD
(insert>name>define)

then you can select A1 in Sheet1 and use

=ODD=TRUE

and the CF will work

now you can hide this particular sheet with the isodd formula and nobody
will have a clue


--


Regards,


Peo Sjoblom

Can't you just use =MOD(A1,2) ?

Yes, you could. I included the >0 in my reply just to show some
uniformity
with the even numbers formula.

=MOD(A1,2)=0
=MOD(A1,2)>0

--
Biff
Microsoft Excel MVP

"David Biddulph" <groups [at] biddulph.org.uk> wrote in
message

Do you need the =1, Carlo? Can't you just use =MOD(A1,2) ?

I don't know the formula isodd, but you can use this formula:
=MOD(A1,2)=1
which gives you false for any even and a true for any odd number.


I have consective numbers in column A. In column B is formula =isodd
which returns True or False, and this works for me but I have tried
to
use =isodd and =iseven as a conditional format formula with no luck.
My question is, is it possible to use either of these arguments in a
conditional format formula. I have tried severial syntax with no
luck.
I most often get error message "May not use referance to other sheet
or book...." I know you guys can crack this.

BigD- Hide quoted text -

- Show quoted text -

Thanks everyone! The formulas work and solve the problem nicely.
In response to Valko I do have the analysis tool pack installed on all
of my machines which makes me wonder if the isodd command is for some
reason not available in conditional formating. I do not know enough
about excel to test the command in cf but I am researching it.

Regards

BigD

 

[Gord Dibben]

A question here for the experts.

I have an add-in that I reference in a workbook using VBE and Tools>References.

UDF's from this add-in can then be used in a CF formula.

If I reference FUNCRES.xla I still cannot use the ATP functions in CF formulas.

I assume the add-ins installed with Excel act differently from a regular
user-built add-in.

Correct?


Gord Dibben MS Excel MVP

 

[Pete_UK]

Gord,

you ARE one of the experts !! <vbg>

Pete

A question here for the experts.

I have an add-in that I reference in a workbook using VBE and Tools>References.

UDF's from this add-in can then be used in a CF formula.

If I reference FUNCRES.xla I still cannot use the ATP functions in CF formulas.

I assume the add-ins installed with Excel act differently from a regular
user-built add-in.

Correct?

Gord Dibben MS Excel MVP







- Show quoted text -

 

[Gord Dibben]

Pete

I may be an MVP but would not consider myself an expert in Excel.

More like an intermediate user.

Thanks for the vote of confidence, however<g>


Gord

 

[T. Valko]

A question here for the experts.

Well, that eliminates me. But I do know a little about this subject!

You can use functions from the ATP in CF *but* you can't use them directly
or Excel complains like the OP pointed out. You have to use them in a named
formula.

Insert>Name>Define
Name: Odd
Refers to: =ISODD(INDIRECT("A"&ROW()))

Set CF
Select the range (assuming A1:An)
Formula Is: =Odd

If there's an easy workaround that bypasses the ATP functions then I'd use
that such as in this case using the MOD formulas.

 

[Gord Dibben]

I know you can use a named range but that was not the question.

To access a UDF from a user-created add-in all I have to do is add a reference
to that add-in in Tools>References then call it directly in CF

Typical example..........add-in has this UDF and the add-in is referenced by my
open workbook.

Function IsFormula(Cell)
IsFormula = Cell.HasFormula
End Function

In CF I use Formula is: =IsFormula(cellref) to return true or false

Or =DocProps("Author")="Gord Dibben" using the DocProps function

This will not work with Excel add-ins AFAICS


Gord

 

[T. Valko]

Ok, I see what you mean. I don't have an answer for that.

 

[BIG D]

Ok, I see what you mean. I don't have an answer for that.

--
Biff
Microsoft Excel MVP

"Gord Dibben" <gorddibbATshawDOTca> wrote in message











- Show quoted text -

You Guys are working rocket science and all I wanted to do was crack a
rock. I think I'll just hit it with a hammer! I really appreciate you
all working on this and letting me and others learn from you. Some of
this is way over my head but some of it I can follow and put to use
later.

Respectfully

BigD

 

[Gord Dibben]

Sorry about going off on a bit of a tangent.

My query was off-topic.

Use the named range method Biff posted.


Gord