Conditional Formatting highlight consecutive cells above a specific value

  • Hello all,

    I'm trying to apply conditional formatting across this entire sheet to highlight three consecutive cells above 4.5.

    I thought I had it with = and(e3>4.5,f3>4.5,g3>4.5)

    it doesn't work. I've tried a couple of different formulas and it always highlights pieces rather than the three or more in a row that are above the 4.5 constant



    help please! I'm sure someone here can get it.. I sure couldn't..

  • You need to test 3 times, and exclude groups that have text (like NA), so:

    AND ( currcell > 4.5 , celltoright > 4.5 , cell2toright > 4.5 )

    AND ( celltoleft > 4.5 , currcell > 4.5 , celltoright > 4.5 )

    AND ( cell2toleft > 4.5 , celltoleft > 4.5 , currcell > 4.5 )

    combined with a SUMPRODUCT with NOT and ISTEXT to make sure all 3 cells are not text.


    See attached.


    Trial Sheet for Brooke.xlsx

    Cheers, Glenn.


    Beauty is in the eye of the beer-holder.

  • Be aware that a SUMPRODUCT covering the 3 ANDs and the text testing is possible, but I'm out of time today. Will post that tomorrow.

    Cheers, Glenn.


    Beauty is in the eye of the beer-holder.

  • One CF formula of:
    =OR(SUMPRODUCT(--(C2:E2>4.5))+SUMPRODUCT(--NOT((ISTEXT(C2:E2))))=6,SUMPRODUCT(--(D2:F2>4.5))+SUMPRODUCT(--NOT((ISTEXT(D2:F2))))=6,SUMPRODUCT(--(E2:G2>4.5))+SUMPRODUCT(--NOT((ISTEXT(E2:G2))))=6)

    does the trick. See attached

    Trial Sheet for Brooke.xlsx

    Cheers, Glenn.


    Beauty is in the eye of the beer-holder.

Participate now!

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