Hi all,

Right I am having a problem with a manning spreadsheet that we have at work. Basically I am trying to add some coding that if the box has a colour in it it will return a 0 and if it is white it will return a 1. So that we can have a tally at the bottom for man power available for each day of the month. I have found a few bits that are not working correctly, And I do not want to revert to a 1 in the box when you are in as it lookes disgusting.

Any help here would be amazing cheers.

To do what you've stated would require programmatically examining the color content of each cell. Not only is that going to be inefficient, it will be impossible if the color changes are occurring based on Conditional Formatting.

If CF is in play, that means you're using a formula of some kind to apply the CF, so you would need t build that same "test" into your macro rather than trying to examine colors.

Moreover, if we could see what you're really trying accomplish in this spreadsheet we may be able to suggest a completely different approach that is simpler.

Post your workbook and explain the goal you're trying accomplish, preferably NOT in relation to colors, but in relation to what the data on the sheet represents.

JBeaucaire thank you for your reply I have included a snip from the sheet that I am referring to.

As you can see there are some boxes with 1 in as what the person before me was going to implement but that lookes so untidy and unprofesional. So what I want as shows is if the box is white then the number o nthe bottom that is linked to all the boxes above will calculate it a 1. and when it is green or red or blue it is a 0 so we can see how many of our people aregoing to be in each day.

Here's a UDF that can be installed in a spreadsheet to give you the capability to count cells that are still NOT colored, cells that have the default clear fill. This goes in a standard code module in Excel.


Function CountNonColors(CellRange As Range) As Long
Dim cell As Range

Application.Volatile

For Each cell In CellRange
If cell.Interior.ColorIndex = -4142 Then
CountNonColors = CountNonColors + 1
End If
Next cell

End Function


Once that is installed, you can use it in a cell like any other function. For instance in D80:

=CountNonColors(D56 : D59) + CountNonColors(D61 : D69)

Thank you JB my spreadsheet is now the bomb