Excell coding

[Phoenix1664]

Hi all,Right i am having a problem with a manning spreadsheet that we have at work. Basicaly 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.

 

[Trevor G]

Welcome to the forum,
You want to record a macro and apply the colour to a cell thsi will then give you the general code you need then you can adapt it like the following example

Sub Macro1()
Range("D3").Select
Do Until ActiveCell.Value = ""
If ActiveCell.Interior.ColorIndex = xlNone Then ActiveCell.Offset(0, 1) = 1
ActiveCell.Offset(1, 0).Select
Loop
End Sub

 

[Phoenix1664]

Thank you trevor,I have used the code to creat a macro but i am not the best with all this how do i tell it to display the results in for instance f50?

 

[Trevor G]

You could use the following like a formula to do this, you copy and paste the code into a MOdule sheet in your workbook. Then use the front end of excel and use the Insert Function and change the category to User Defined and select the Function and then select the cells you want to check for and then a cell that contains the colour

Function CountColor(Rng As Range, RngColor As Range) As Integer
Dim cll As Range, clr As Long
clr = RngColor.Range("A1").Interior.Color
For Each cll In Rng
If cll.Interior.Color = clr Then
CountColor = CountColor + 1
End If
Next cll
End Function

So the formula would look like this in your chosen cell =CountColor(C4:C12,A1)