Return the last number in a range that is not in a coloured cell

  • A Formula approach required that would add a 1 or any other number to the last cell above containing a number, but ignore numbers in coloured cells:


    For instance, something that would return the maximum number in the range above, but ignoring numbers in coloured cells.


    I would like to have formulas that would add 1 to the previous number in a column that is not in a cell with a coloured background. The numbers in the coloured cells are manual numbers and not results of formulas.
    [ATTACH=CONFIG]70196[/ATTACH]


    I have a function that returns the colour index of a cell that can be used.

    I used this function like:
    =IF(CellColorIndex(B6)>0,"",MAX($B$5:B5)+1)
    This works if there are no numbers in the coloured cells but formulas (which is not what is required) – see attached file.

    In other words, what is required is that the numbers in the coloured cells are ignored when determining the next number.

  • Re: Return the last number in a range that is not in a coloured cell


    I am not aware of any way to do what you ask without using VBA.


    It occurs to me that this is precisely the type of scenario that makes VBA so useful.


    Formulae only work on cell values, not how that value is displayed, so I fear you're on a hiding to nothing.

  • Re: Return the last number in a range that is not in a coloured cell


    Quote from Infomage;778157

    I am not aware of any way to do what you ask without using VBA.


    It occurs to me that this is precisely the type of scenario that makes VBA so useful.


    Formulae only work on cell values, not how that value is displayed, so I fear you're on a hiding to nothing.


    Infomage:
    Thank you for your reply, I will have to think how this can be done with VBA.


    Kind Regards,
    vanhunk