If white, add 1 to total!

HI. Can someone please help me with a wee prob.

I have a speadsheet in Excel and some of the cells are grey and the others are white. (No cell is ever a fixed colour - they can be changed from grey/white to white/grey anytime the user likes).

The very last row of cells in my sheet remain white and this is the 'TOTAL' row.
What i am hoping to do is, the number of white cells in each column is to be displayed in the appropriate column in the 'TOTAL' row.
For example; if Column D had 7 white cells and 4 gray cells, then the number 7 will be displayed in the TOTAL row, in Column D. if Column J had 2 white cells and 8 gray cells, then the number 2 will be displayed in the TOTAL row, in Column J)

I've tried something like:

If Cells white Then
add 1 to TOTAL

The TOTAL row runs from D34 to AJ34

The areas that can be either colour run from D to AJ and down to row 33

Any help would be much appreciated

Thanks

You can´t count the interior colour of cells with formulas.

You need something like the following:


Public Function COLOURCOUNT(rnArea As Range, lnColourIndex As Long)
Dim rnCell As Range, lnCount As Long
For Each rnCell In rnArea
If rnCell.Interior.ColorIndex = lnColourIndex Then
lnCount = lnCount + 1
End If
Next rnCell
COLOURCOUNT = lnCount
End Function


First time You enter it make sure You enter it with uppercase. In that way Excel will "remember" it, meaning You can enter it next time with lowercases and Excel will convert it to uppercases (like the way with the built-in)

If You want to count non-colours, i e the default white then You need to use the constant -4142