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
Display More