# Thread: Solved: % using the color of a cell

1. ## Solved: % using the color of a cell

I'm using a dutch excell version
I want to get a % by using the color of a cell. I have white, green en red cells.
I want the next formula

red cells/(red cells + green cells)

I found the next formula

=sumproduct(--(colorindex(range)=3)) that must count the red cells but as i translated it to

= somproduct(--(kleurindex(range)=3)) i get #Naam?

what is wrong?

Ger  Reply With Quote

2. Colorindex is a UDF Ger, so that should not be translated.

Have you put the UDF code in your workbook?  Reply With Quote

3. =SOMPRODUCT(--(colorindex(E6:E15)=4))
also gives #naam?
What do you mean by putting the UDF code in the workbook?
I only put this formala in a cell

Ger  Reply With Quote

4. Are the normal functions in excel in dutch, or english?
NB: I couldn't get the colourindex thing to work.

or example do these work?

[VBA]=SUM(A1:A4)[/VBA]
or
[VBA]=COUNT(A1:A4)[/VBA]  Reply With Quote

5. all formulas in cells are in dutch.  Reply With Quote

6. i only ask as i coudln't get:
=sumproduct(--(colorindex(range)=3))
to work at all, or should there be some values where the -- are?  Reply With Quote

7. Colorindedx is a UDF, so you have to put the code in the workbook. Excel does not have a ColorIndex function built in, Where did you get the formula from, was it http://www.xldynamic.com/source/xld.ColourCounter.html?  Reply With Quote

8. UDF = User Defined Function?  Reply With Quote

9. Yes.  Reply With Quote

10. These functions might help...

[vba]Public Function SUMCOLOR(rCheck As Range, _
Optional iColorIndex As Long = -4142, _
Optional iFontIndex As Long = -4105) _
As Variant
Dim zCell As Range, iTemp As Variant
For Each zCell In rCheck.Cells
If zCell.Interior.ColorIndex = iColorIndex Or zCell.Interior.ColorIndex = -4142 Then
If iColorIndex <> zCell.Interior.ColorIndex Then GoTo Skip_zCell
If zCell.Font.ColorIndex = iFontIndex Or zCell.Font.ColorIndex = -4105 Then
If iFontIndex <> zCell.Font.ColorIndex Then GoTo Skip_zCell
If IsNumeric(zCell.Value) Then iTemp = iTemp + zCell.Value
End If
End If
Skip_zCell:
Next
SUMCOLOR = 0
If Not IsEmpty(iTemp) Then SUMCOLOR = iTemp
End Function

Public Function COUNTCOLOR(rCheck As Range, _
Optional iColorIndex As Long = -4142, _
Optional iFontIndex As Long = -4105) _
As Variant
Dim zCell As Range, iTemp As Variant
For Each zCell In rCheck.Cells
If zCell.Interior.ColorIndex = iColorIndex Or zCell.Interior.ColorIndex = -4142 Then
If iColorIndex <> zCell.Interior.ColorIndex Then GoTo Skip_zCell
If zCell.Font.ColorIndex = iFontIndex Or zCell.Font.ColorIndex = -4105 Then
If iFontIndex <> zCell.Font.ColorIndex Then GoTo Skip_zCell
If IsNumeric(zCell.Value) Then iTemp = iTemp + 1
End If
End If
Skip_zCell:
Next
COUNTCOLOR = 0
If Not IsEmpty(iTemp) Then COUNTCOLOR = iTemp
End Function

Function GETINDEXCOLOR(rCheck As Range) As Long
If rCheck.Cells.Count > 1 Then Exit Function
GETINDEXCOLOR = rCheck.Interior.ColorIndex
End Function

Function GETFONTCOLOR(rCheck As Range) As Long
If rCheck.Cells.Count > 1 Then Exit Function
GETFONTCOLOR = rCheck.Font.ColorIndex
End Function[/vba]

Use the two bottom functions (GETINDEXCOLOR, GETFONTCOLOR) to get the index number of the color (fill and font). (Originally done here.)

HTH  Reply With Quote

11. ## I don't understand it

I copied the udf to the worksheet but without any result.

So i posted an example.

It is used to calculated how many trains in a serie this week a started in time.
If there is input in a cell it means the train was to late (red cell).
Date the are to come are white (see week 12)

Ger  Reply With Quote

12. You didn't, there is no ColorIndex function in that workbook. But I notice that you have merged cells, it isn't designed to work with merged cells.  Reply With Quote

13. I'm confused. If i look with vba in the worksheet i see the thingi copied from above. Is this the wrong place.
please help.     Reply With Quote

14. You copied Zacks functions into THisWorkbook (which practically makes them unaccesible), but the workbook is calling a UDF you have not implemented.  Reply With Quote

15. You need to take the code out of the ThisWorkbook module, and place it into a standard module, i.e. your Module1. Then your Sumproduct() formula could be reduced to ...

`=IF(CountColor(D6:D15,3)=0,1,CountColor(D6:D15,4)/CountColor(D6:D15,3))`
Unmerge your cells. Then explain in detail what you are trying to sum/count and under what conditions (not sure if you are summing or counting).  Reply With Quote

16. ## almost there

Ok,

i unmerged the cells, copied the vba code to the module sheet and changed the , into ; in the formula =ALS(CountColor(G6:G10;3)=0;1;CountColor(G6:G10;4)/(CountColor(G6:G10;3)+CountColor(G6:G10;4))).

But the answer is always 100%. See the example. Ger  Reply With Quote

17. LOL!It is not cell colouring that you have used, it is conditional formatting.

You either need to check the CF conditions, or use this technique http://www.xldynamic.com/source/xld.CFConditions.html  Reply With Quote

18. It is to difficult for a simple user as i'm. So thanks for all the help.

Ger  Reply With Quote

19. As I said, you can do it by testing the same conditions as the CF

=SUMPRODUCT(--(D\$6:D\$10=""),--(\$A\$1>=B\$6:B\$10))/SUMPRODUCT(--((D\$6:D\$10="")+(D\$6:D\$10>"")))

in cell D11  Reply With Quote

20. Thanks, this works.

I'm a simple user, can you explain where the -- stands for?

Ger  Reply With Quote

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•