How to make a cell turn red if the value is a prime number

[popcornrulez]

I know how to do the Conditional Formatting stuff for cells, but I need to make a cell that turns red if the number is a prime number. I just need to know if there is a formula that returns true if the value is a prime number and false if it isn't. And if you guys know of an add-in for excel that has a similar formula please post the link.


popcornrulez

 

[BobUmlas]

CF formula (if cell is A1):
=OR(A1=2,A1=3,ISNA(MATCH(TRUE,A1/ROW(INDIRECT("2:"&INT(SQRT(A1))))=INT(A1/ROW(INDIRECT("2:"&INT(SQRT(A1))))),0)))

 

[popcornrulez]

CF formula (if cell is A1):
=OR(A1=2,A1=3,ISNA(MATCH(TRUE,A1/ROW(INDIRECT("2:"&INT(SQRT(A1))))=INT(A1/ROW(INDIRECT("2:"&INT(SQRT(A1))))),0)))

I need to be able to implement the formula into a conditional formatting rule, and I tried this formula and it said true on 7, (which is right), and true on 9 also, (which isn't right).

 

[Jonmo1]

I found this with a quick Google Search using "Excel IsPrime"
Test if a Number is Prime using a Formula in Microsoft Excel

to convert that to a TRUE/FALSE instead of Prime or Not Prime so that it can be used in conditional formatting..
=OR(A1=2,AND(MOD(A1,ROW(INDIRECT("2:"&ROUNDUP(SQRT(A1),0))))<>0))

 

[BobUmlas]

It works exactly right as is in a CF formula, but as a worksheet formula it must be array-entered (Ctrl+Shift+Enter). it shows FALSE for 9 if done as I said.