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.

Function CellColorIndex(InRange As Range, Optional OfText As Boolean) As Integer
     '
     ' This function returns the ColorIndex value of a the Interior
     ' (background) of a cell, or, if OfText is true, of the Font in the cell.
     '
    
    If OfText = True Then
        CellColorIndex = InRange.Font.ColorIndex
    Else
        CellColorIndex = InRange.Interior.ColorIndex
    End If
    
End Function

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

I assume this can not be done, or am I mistaken.

Kind regards,
van hunk

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