Conditional formatting

[Gmunro]

TRACKER.xlsxTRACKER.xlsx

Hi All

Please could you help with the attached workbook. I am trying to get my head around conditional formatting and failing miserably. I am trying to highlight Progress by colours dependent upon the Progress made from a baseline test result.
Test Result is in 'L'
Need the result to be identified in coloumns 'X, AF, AN, AO, AX, BF, BN, BO & BW'
My Range is located in 'CD' Going From less than 3 to 8+

Progress is determined by sub level movement, this is indicated for reference only in 'M,N,O'

<5 sub levels = less than expected (want to indicate in RED)
5 sub levels = expected (want to indicate in Orange)
7 sublevels = good (want to indicate in Blue)
9 sub levels = outstanding (want to indicate in Purple)

My main issue is the flucting point of all rows, (L)

A sublevel is classed as a single move, so 3c - 3b = 1 sub level & 5c - 7c = 6 sublevels

I want each column to take itsvalue from L and not the previous Progress indicator.

I think I have explained all of that, you help would be amazing on this issue as it would become an invaluable piece of work for my students.

Many thanks

TRACKER.xlsxGraeme

[Gmunro]

This isnt looking hopeful as I have been waiting 24hrs, is this not possible? Please please please

[NBVC]

I am little confused about the whole setup and what you want coloured.. but maybe this will help.

To determine sub levels use a formula like:

=MATCH(L5,$CD$4:$CD$23,0)-MATCH(L4,$CD$4:$CD$23,0)

where L4 and L5 are the levels to compare. so if L4 has 5c and L5 has 7c, then the result will be 6 (based on your column CD setup).