|
|
|
|
Full Member
|
|
Apr 2, 2013, 01:22 AM
|
|
Excel coding help
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.
|
|
|
Software Expert
|
|
Apr 2, 2013, 11:30 AM
|
|
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.
|
|
|
Full Member
|
|
Apr 3, 2013, 12:30 AM
|
|
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.
|
|
|
Software Expert
|
|
Apr 6, 2013, 07:10 AM
|
|
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.
Code:
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)
|
|
|
Full Member
|
|
Apr 11, 2013, 12:14 AM
|
|
Thank you JB my spreadsheet is now the bomb
|
|
Question Tools |
Search this Question |
|
|
Add your answer here.
Check out some similar questions!
Color coding on data line does not match jack color coding
[ 1 Answers ]
Installing cat5 data standard blue/white-blue, green/white-green, orange/white-orange, and brown/white brown but the wall mount I purchased has black, yellow, white, green, blue, orange, brown, and green. What color line do I connect Where?
Color coding on line does not match jack color coding
[ 11 Answers ]
The line going into the old phone jack has orange, green, blue and 3 white wires in it. Bought a new jack at Radio Shack and the colors of the wires in the replacement jack are red, green, black and yellow.
Verizon repair was at best, snotty in telling me "we cannot tell you how to do repairs...
Listview coding
[ 2 Answers ]
What code should I write so that.. if I click on any particular item on listview its corresponding contents from database :confused: are displayed in another listview
Coding a c++ program
[ 2 Answers ]
I need a fast help to code the following program which is really easy:
I have to develop a program to analyze one or more numbers entered by a user and see if the number is a prime # or a perfect # and out put a list of divisors of all perfect numbers and noneprime numbers.
Hint: A prime # is...
View more questions
Search
|