ISEVEN Conditional Format Error

[sbeatton]

I am experiencing a problem with Conditional Formatting. I am trying to format a Column based upon a cell value being even. When I make the conditional format, FORMULA IS = ISEVEN($A3), i am getting the error "You may not use references to other worksheets or workbooks for Conditional Formatting criteria"

Has anyone used ISEVEN in conditional formatting at all?

 

[jindon]

Do you need to use IsEven function ?

=Mod($A3,2)=0

 

[xenou]

Hmm, not sure...maybe because you need to reference the analysis toolpak which is an add-in?

You can use this formula instead:

=$A3/2=INT($A3/2)

(borrowed from:
http://en.allexperts.com/q/Excel-1059/counting-odd-numbers.htm

 

[sbeatton]

Thanks all.
Would you believe I also already use =MOD(ROW(),2) but had one of those moments.

 

[lenze]

This is a good point! Non-resident functions will not work in CF. So because ISEVEN is from the Analysis ToolPak, CF can not use it. Likewise, CF can not use a UDF.

lenze

 

[Tom Urtis]

Likewise, CF can not use a UDF.

Yes it can, example to CF cells with comments:

Public Function Test(rng As Range) As Boolean
Test = IIf(rng.Comment Is Nothing, 0, 1)
End Function


CF Formula is
=test(A1)

 

[RoryA]

You can also use add-in functions if you use them in a defined name and then use that defined name in the CF formula.

 

[lenze]

Yes it can, example to CF cells with comments:

Public Function Test(rng As Range) As Boolean
Test = IIf(rng.Comment Is Nothing, 0, 1)
End Function


CF Formula is
=test(A1)

Thanks Tom, I didn't know that. For some reason, I was under the impression they would not work!!

lenze