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.
I have a function that returns the colour index of a cell that can be used.
I used this function like:
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
- CellColorIndex = InRange.Interior.ColorIndex
- End If
- End Function