Conditional Formatting; Tricky

    People
    I would appreciate some assistance even if it's just a different perspective.
    I have a range of cells across the page, with each cell representing a time across the duration of a shift - say from 6.00am to 2.00pm.
    I wish to populate the cells with a letter "B", to begin and "E" to end which is all ok if there is only one occasion or period of this occurring, where it gets interesting is where say at 6.30am a task began and it was completed by 9.00am. Time passes and the task is again re-visited, this time say from 11.45am to 11.55am. In which case we have two sets of "B" and "E".
    I'm thinking using match function to see which is closer to the cell requiring formatting (they all do).
    Many thanks for any assistance.
    Regards
    Kiwifinny

    I went very simply to count the occurrences of letters and format if the number of letters to the left was an odd number.
    Thanks anyway.

    Copy this conditional formatting formula into cell c7, choose your color, and then apply it to the range:


    =IF(ISEVEN(COUNTA($C7:C7)),0,LOOKUP(2,1/($C7:C7=LOOKUP(2,1/($C7:C7<>""),$C7:C7)),COLUMN($C7:C7)-COLUMN($C7)+1))

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login