Microsoft Excel - new versions

diomed

My question is hardly worthy of a new thread.
I have Excel 2003. In that version you can use three conditions in Conditional Formatting - this means you could format cells in three formats (and each format has font, border, colour).
I know there are newer versions of Excel. I've had a look at Excel books in Easons but the examples do not help.

Question 1: how many format conditions can you use in the newer versions? I would like to format cell in up to ten different colours based on duplicated text values using Countif or similar.

A data example might help make sense.

Horse Group
Sadler's Wells 1
Rainbow Quest 2
Rainbow Quest 2
Cadeaux Genereux
Sadler's Wells 1
Never Bend 4
Northern Dancer 3
Lalun 5
Secreto
Fairy King
Rainbow Quest 2
Lalun 5
Man O' War 6
Never Bend 4
Kahyasi
Northern Dancer 3
Hyperion
Val De Loir
Man O' War 6
Man O' War 6


I am trying to highlight in colour duplicated horses in pedigrees (called inbreeding/linebreeding).
The pedigree chart will be in A4 format, and much more complex than the simple example above, probably a six-generation chart with 127 horses.
In the example below there are six groups: 1 (2); 2(3); 3(2); 4 (2); 5(2); 6(3) = 6 groups with a total of 14 duplicated horses.
In the pedigree (family tree) I will not have Group numbers (1,2,3,4,5,6 in example) so would like to format conditionally using names only.

Using Countif for count > 1 will highlight all 14 duplicated names as one group. No good. I want 6 groups in different colours.

Question 2: Is there a way to highlight six or more groups using text only (horse names).

srsly78

If you write some code in vbscript you can have as many colours as you want, even with 2003. You can also get plugins that let you use "proper" frameworks like .NET with excel.

Later versions make it easier to work with .net+excel, no plugins needed.

stevenmu

Just tried with Excel 2010 and you seem to be able to do as many as you want. I think it was the same for Excel 2007 too.

SimonTemplar

If you are stuck with 2003, your best and easiest option would be to use VBA code which pretty much gives you unlimited colours with a variety of conditions.

If you have never used VBA before and need assistence, send me a PM.

diomed

Thanks very much to all of you for your kind replies.

I think it is time to buy a modern version of Microsoft Office.
I have not used Visual Basic in Excel. My only coding is using dbase (actually FoxPro). I use an old version of the horse pedigree program TesioPower to gather / input horses pedigrees, and then analyse outside it using FoxPro on the TesioPower dbf files.

Stevenmu
Did you conditional format using numbers or horse names? I would like to do the conditional formatting on a pedigree chart without numbers (it won’t have numbers, only horse names).

If I can’t get the result I want using Excel menus I’ll have a go at VBA (probably asking for help).

stevenmu

diomed wrote: »

Stevenmu
Did you conditional format using numbers or horse names? I would like to do the conditional formatting on a pedigree chart without numbers (it won’t have numbers, only horse names).

If I can’t get the result I want using Excel menus I’ll have a go at VBA (probably asking for help).

Oh, I did it on numbers, but it would work just the same for horse names.

You can download free trials of Office btw if you want to try a newer version before comitting to buying it.
Pro: http://technet.microsoft.com/en-ie/evalcenter/jj192782.aspx
Home: http://office.microsoft.com/en-ie/try/