Multiple conditional formating per column???

**farmerTom** · 04-23-2007, 11:44 PM

I have a condition allreay that makes the odd numbers red, I would like to make the prime numbers green (all the lower primes except 2 are odd numbers). I think a If condition preceding the Mod would work to find them, but making the font green is another mater!

This is the column(s) I enter the data into so in the cell formula box is the lotto number, ????????

=(MOD(B1:B163,2)<>0)*1

**PeterB** · 04-24-2007, 06:07 AM

I think what you need to do is 'label' each of your cells as Prime or Not Prime. By this I mean an adjacent cell contains e.g. "Prime" or "Not Prime". You then use the Conditional Formatting Condition 1 to detect the Prime cells (Condition 2 is then used to detect the Odd numbers).

How should you go about 'labelling' the cells ... Well, you can either do it:
1. Manually.
2. Use a macro to set the cell to "Prime" if the cell contents when divided by the Index number of a loop (that loops between 2 and half the maximum value that you will have in your cells) is an Integer.
3. Use a lookup table, which is really a semi-manual, semi-automatic method.

**PeterB** · 04-24-2007, 07:18 AM

You might want to try the attached file to see if you can use the relevant bits. (The large area with either empty cells or cells with "1" in them were added purely to check the list of primes.)

**Bryan Hessey** · 04-24-2007, 07:31 AM

Originally Posted by PeterB

You might want to try the attached file to see if you can use the relevant bits. (The large area with either empty cells or cells with "1" in them were added purely to check the list of primes.)

Hi Peter,

I think that you need to include 47 in your 'Primes' list

---

**PeterB** · 04-24-2007, 07:56 AM

Ooops! Thanks Bryan ... Don't know how I missed that one (brain not functioning). New zipfile attached.

**farmerTom** · 04-25-2007, 01:07 AM

Accidently answered my own question..... I didn't notice the Add Button in the Conditional Format Window.

**Bryan Hessey** · 04-24-2007, 07:18 AM

Originally Posted by PeterB

I think what you need to do is 'label' each of your cells as Prime or Not Prime. By this I mean an adjacent cell contains e.g. "Prime" or "Not Prime". You then use the Conditional Formatting Condition 1 to detect the Prime cells (Condition 2 is then used to detect the Odd numbers).

How should you go about 'labelling' the cells ... Well, you can either do it:
1. Manually.
2. Use a macro to set the cell to "Prime" if the cell contents when divided by the Index number of a loop (that loops between 2 and half the maximum value that you will have in your cells) is an Integer.
3. Use a lookup table, which is really a semi-manual, semi-automatic method.

Hi,

for numbers 1 to 100 (or so) you can use something like

=IF(IF(GCD($A1,2)=$A1,1,GCD($A1,2))+IF(GCD($A1,3)=$A1,1,GCD($A1,3))+IF(GCD($A1,5)=$A1,1,GCD($A1,5))+IF(GCD($A1,7)=$A1,1,GCD($A1,7))+IF(GCD($A1,11)=$A1,1,GCD($A1,11))+IF(GCD($A1,13)=$A1,1,GCD($A1,13))+IF(GCD($A1,17)=$A1,1,GCD($A1,17))+IF(GCD($A1,19)=$A1,1,GCD($A1,19))+IF(GCD($A1,23)=$A1,1,GCD($A1,23))=9,"Prime","Not")

which someone should be able to shorten. GCD is in the Analysis Toolpak Addins (Tools, Addins and tick the Analysis)

hth
---
added

on reflection I believe the formula covers up to 29*29 ( 841 ) which will be incorrectly reported as Prime

---