Can the color of a cell have a value?
Posted by GLITZ on December 27, 2000 2:03 PM
In row format I have employee production. Each column is a unit of time (hour) in a 8 hour work day. I want to apply specific "highlight" to a cell to represent what type of "unit" the employee produced.
Idealy I would like to total all same color cells in an employees row to produce the information I want.
Any input?
Thanks
Posted by Celia on December 27, 2000 4:16 PM
I think VBA is required to sum cells by colors.
To get an idea of what is required, have a look at CountColors_VBA.xls at :-
(broken link)
Alternatively, perhaps a worksheet formula could be created that incorporates the same criteria used to assign the colors.
Celia
Posted by Dave on December 27, 2000 9:35 PM
Hi GLIZT
You may be able to use a Custom function like this. Just be aware it wont pick up Conditional formatting.
Function MyColorSum(ClrToSum As Range, SumRng As Range) As Double
Dim MyRange As Range, MyClr As Integer
MyClr = ClrToSum.Interior.ColorIndex
For Each MyRange In SumRng
If MyRange.Interior.ColorIndex = MyClr Then
MyColorSum = MyColorSum + MyRange
End If
Next
MyColorSum = MyColorSum
End Function
Dave
OzGrid Business Applications
Posted by GLITZ on December 28, 2000 4:32 AM
I love how you guy's can pin point and give answers to my specific problems!
Where do I put a function? Is it all in one cell?
Posted by GLITZ on December 28, 2000 5:07 AM
DO I NEED TO LABEL MY COLORS IN MY CODE?
EXAMPLE: I'm only using 4 colors. Should I give each color a number value?
Posted by GLITZ on December 28, 2000 12:53 PM
DID IT ! but have 1 more question
I did it!!!!
That's unreal Dave!!
Do you have one up your sleeve for counting how many times a certain cell color appears?
Thanks again
Posted by Dave on December 28, 2000 9:48 PM
Re: DID IT ! but have 1 more question
Hi GLITZ
>>Where do I put a function? Is it all in one cell?
Sorry GLITZ, sometimes I assume too much.
Push Alt+F11, go to Insert>Module and paste in the code, Push Alt+Q to return to Excel and Save.
*Tip: consider placing it in a module Within your "Personal Workbook" then it will always be available.
Push Alt+I+F and select your new function from "UserDefined" under "Function Catergories"
As you have no doubt realized all you will need to do to determin the color is select any cell that has the color you want to Count or Sum.
The only drawback other than it not working on Conditional formatting is it will be slow to calculate large ranges.
- OzGrid Business Applications
Posted by GLITZ on December 29, 2000 4:15 AM
Re: DID IT !
Thank You
I didn't put my functions in a personal file of any kind, but they seem to be available in my user defined section......will they always be there?
Are these functions only available in the user defined area b/c I am still in the original sheet or a renamed version of the original?
Posted by Dave on December 30, 2000 3:17 AM
Re: DID IT !
Re-will they always be there?
No, they will only be in the workbook you placed them in, hence my suggestion to place then in a module within your personal macro workbook.
- OzGrid Business Applications
