ExcelBanter - occasions

You could do the whole thing in VBA. A hybrid (and one that would lend
itself to reuse perhaps) solution would be to create a user defined
function that would tell you whether the given cell has a red cell after it.

Note that I'm assuming by White and Red you mean the interior of the
cell has been set to White and Red - not the cell interior merely has
no colour.

Copy this into a VBA standard module

Public Function fcnIAmRedAndTheNextCellIsWhite(rng As Range) As Integer
'returns 1 if this cell is Red and the Next cell is White
With rng
If .Interior.Color = vbRed _
And .Parent.Cells(.Row + 1, _
.Column).Interior.Color = vbWhite _
Then fcnIAmRedAndTheNextCellIsWhite = 1
End With
End Function

And place this into the top cell of the column next to your red and
white rows (for example) "=fcnIAmRedAndTheNextCellIsWhite(A2)"
And copy down. Then simply sum it.

If you want to do this in one fell swoop instead i.e. for your entire
range, just add the below code to your module:

Public Function fcnDoForAllCells(rng As Range) As Integer
Dim c As Range
Dim iCount As Integer
'returns total of if this cell is Red and the Next cell is White
For Each c In rng
iCount = iCount + fcnIAmRedAndTheNextCellIsWhite(c)
Next c
fcnDoForAllCells = iCount
End Function

And place the below formula into a cell

=fcnDoForAllCells(D5:D369)

Note that this will not refresh automatically should colours change.

HTH,
Gareth

alymcmorland wrote:
Hi,

i have a column of cells D5:D369 to be exact of which i need to count
the amount of times red coloured cells are followed by white coloured
cells and to output a 1 and then for that to be incremented each time a
red cell is followed by a white one in the range.

If anyone could provide code that would be amazing as i have no idea
how to do it myself!

Thanks