Highlight cells four different colours .

[I need excel help]

Hello all ,

I have greatfully received these two macros in this sheet that gets data from each date
but one row above .

The data comes from cols AL and AO .

Hopefully I am wanting to add four different colours to the macro named FIFTYBACKWORKS
or the other one .

The 4 x colours represented come from col AB .

Good - brown
Dead - green
Slow - Light blue
Heavy - blue

I have approx 450,000 rows and the two current macros do this on my computer in about
13 minutes , which is fine .

Also hoping that if this is possible it wont turn in a very long time to churn through . So looking for
maximum speed if possible if at all .

Many thanks so far .

[I need excel help]

Sorry forgot sheet .

[alansidman]

Here is a stand alone VBA that you can insert (if you desire), into an existing Macro. I was able to get the colors close but in two cases not exact. If you know the color index for each, you can change it in the code. Neither of these steps should be difficult for you to modify this code.

Please Login or Register  to view this content.

[I need excel help]

Thanks for that but im wanting to colour cols BJ onwards , not cols AL and AO .
I ran a copy of mine , made cells whitw or clear and your macro coloured AL and AO .
Thanks .

[alansidman]

try this and thanks for the clarification.

Please Login or Register  to view this content.

[rchojwa]

building on alansidman solution here are two possible variations:

first variation: use of

Please Login or Register  to view this content.

block

Please Login or Register  to view this content.

second variation: use of

Please Login or Register  to view this content.

function

Please Login or Register  to view this content.

[I need excel help]

Appreciate the effort going in here but this is what im getting in my sample sheet revised .

All colour macros are giving me the top version , mine is the lower sample original version what things hopefully can look
like .

Thanks .

[alansidman]

Please explain the criteria for your example as it is not as explained in your first post. Apparently, there is more to this than you have explained. You have indicated that the the criteria is in column AB. Because we all seem to have misunderstood your explanation, please explain your criteria in detail. I do not understand what determines each color if not column AB.

[I need excel help]

Yes col AB does determine the colour .
Looking at my last sample sheet result cells BJ:BK15 (colour brown) have come from cells AL:AO14 this ran on a Good track in cell AB14 .
Cell AB15 is also Good , 5 for 2.9
Cell AB16 is Slow , 1 for 4 in cells AL16 and AO16 . Result in BJ:BK17 should be lite blue .
Hope this helps .
Thanks .

[davsth]

it doesn't really help your formatting is driven by column AB which the results have done. There must also be a relationship between the values and the track conditions, as you have different shading across a row. How do the values affect the result?
1 and 4 always seem shaded the same colour (slow) regardless of the value in column AB?

You need to explain more fully

[I need excel help]

Hi , ok will explain better .

This horse has had 11 starts in career .
The macros that give me all the data from col BJ and onwards are always referring to its
immediate previous date before , always for every horse , not what conditions are today . I have opened col AP to show start number 1 to 11 .

Cell AP2 is its first start so that is why cell BJ2 is blank , it cant reference any data .
"Cells BJ3 and BK3 are now looking back at its last start on 27/12/2006 where it ran on a Good
"
track running 5th , 3.4 lenghts away . Coloured brown .

"Cells BJ4 and BK4 are looking back at last start on 17/01/2007 also on a good track .
"
Results in cells BJ:BM4 now show its two past results both on Good track .

Cell AP5 it is having its 4th start to date , so we look back at last most previous start which is on
a Slow track for 1st at 4 length win .

So results in cells BJ:BO5 are showing last results now . Previous on Slow (lite Blue) , 2 x
previous before on Good (brown) .

Next start number 5 will show previous last 4 runs . Most previous on Heavy cell AB5 .
Its 2nd for 1.5 lenghts is resulted in cells BJ and BK6 . Then again its Slow and the two previous
Good starts . And so on .

"Go to its very last start on 15/03/2008 , result data is only looking back at its previous 10
"
starts , hence why there are 10 placing and margin results in cells BJ12:CC12 showing full career
with all its placings and margins and hopefully showing for each of these 10 starts one of the 4
colours representing the 4 track conditions .

If I was to enter this horse in a race today it would give its past 11 results .

Many thanks .

[protonLeah]

Try:

Please Login or Register  to view this content.

RaceTrackCondition= AB2:AB12
(it works ok on the wksheet but colors two additional cells as CF formula.

[I need excel help]

Can the functions be turned into a macro , i have approx 450,000 rows , thanks .

[davsth]

I feel that your sample data is also small as it only has 1 horse in it, assuming the data is just multiple examples of the one horse given, and a change in the horse name is sufficient to reset

Please Login or Register  to view this content.

[I need excel help]

Thanks davsth that macro worked perfectly and 100% accurate .
My massive sheet of approx 450,000 rows was done in about 14 mins . Solved .
Thanks very much .